sasproc-sqlenterprise-guide4gl

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


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 ?


Solution

  • 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;