CODEWITHSUNDEEP
sasproc-sql
dingaro
dingaro

Reputation: 2342

How to drop columns with specific suffixes from a SAS table in SAS?

I have table in SAS Enterprise Guide like below:

COL_DT    | COL_5  | ...  | COL_n
----------|--------|------|--------
10MAY2021 | 1      | ...  | xxx
15DEC2021 | 0.5    | ...  | xxx
09APR2020 | 12     | ...  | xxx
...       | ...    | ...  | ...

And I need to remove from above SAS table column which ends with: _DT, _ID, _CP if columns with mentioned suffixes exist

Expected output:

COL_5  | ...  | COL_n
-------|------|-------
1      | ...  | xxx
0.5    | ...  | xxx
12     | ...  | xxx
...    | ...  | ...

How can I do that in SAS Enterprise Guide ?

Upvotes: 0

Views: 1231

Answers (1)

Kermit
Kermit

Reputation: 3117

Create sample data set

data have;
    input col1-col5 col_dt col_id col_cp;
    cards;
1 2 3 4 5 6 7 8 
9 10 11 12 13 14 15 16
;
run;

Create the list of columns to drop using the same technique as in your previous question

proc sql noprint;
    select distinct name into :cols separated by ' '
    from sashelp.vcolumn 
    where upcase(memname)='HAVE' and 
        (upcase(name) like '%^_DT' escape '^' 
        or upcase(name) like '%^_ID' escape '^' 
        or upcase(name) like '%^_CP' escape '^');
quit;

Drop the selected columns

data want;
    set have(drop=&cols.);
run;

As a result

col1 col2 col3 col4 col5
 1     2    3    4    5
 9    10   11   12   13

Alternatively, you can use proc contents instead of the SAS column view

data have;
    input col1-col5 col_dt col_id col_cp;
    cards;
1 2 3 4 5 6 7 8 
9 10 11 12 13 14 15 16
;
run;

proc contents data=have out=content noprint nodetails;run;

proc sql noprint;
    select distinct name into :cols separated by ' '
    from content 
    where upcase(memname)='HAVE' and 
        (upcase(name) like '%^_DT' escape '^' 
        or upcase(name) like '%^_ID' escape '^' 
        or upcase(name) like '%^_CP' escape '^');
quit;

data want;
    set have(drop=&cols.);
run;

Upvotes: 2

Related Questions