I have table in SAS Enterprise Guide like below which are the result of below query:
proc sql;
create table work.table1 as
select COL2, COL2
from my_table
;quit;
COL1 | COL2
-------------------|--------
A_MON_P2022 | 11
A_XXX_P2023 | 12
C_MON_P2020 | 5
S_FAY_MEP_M2020 | 4
... | ...
And I need to remove all characters from "_M2" till the end in COL1, so as a result I need to have something like below:
COL1 | COL2
-------------------|--------
A_MON | 11
A_XXX | 12
C_MON | 5
S_FAY_MEP | 4
... | ...
How can I modify my PROC SQL query to have desire result in SAS Enterprise Guide ?
According to my understanding,what you want is to get rid of the last "_XXX" part.Most character processing function still work in proc sql
step.
findc(col1,"_","b")
means finding the position of "_" in col1."b"
means searching from right to left, instead of from leftto right, regardless of the sign of the startpos argument.
substrn(col1,1,XX)
will return the first to XX characters .
Here is the code:
data have;
input COL1 $20. COL2;
cards;
A_MON_P2022 11
A_XXX_P2023 12
C_MON_P2020 5
S_FAY_MEP_M2020 4
;
run;
proc sql;
create table want as
select substrn(col1,1,findc(col1,"_","b")-1) as col1 ,col2
from have;
quit;