How to remove characters after defined character in PROC SQL in SAS Enterprise Guide?

Question

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 ?

Answer 1

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;