0

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 ?

Kermit
  • 3,112
  • 2
  • 10
  • 34
dingaro
  • 2,156
  • 9
  • 29
  • I think it is an old topic: https://communities.sas.com/t5/SAS-Programming/want-to-drop-variables-with-same-suffix/td-p/201568 – whymath Dec 15 '22 at 17:20

1 Answers1

1

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;
Kermit
  • 3,112
  • 2
  • 10
  • 34
  • Kermit, are you sure that this code is correct ? I used that and columns with mentioned suffexes still exist in my table ? – dingaro Dec 15 '22 at 15:26
  • What is upcase(memname)='HAVE' and sashelp.vcolumn ? – dingaro Dec 15 '22 at 15:26
  • You have to replace the `memname` by the name of your SAS table, in upcase. `sashelp.vcolumn` is a view that provides information about name, type, length, member and library name for all variables in all datasets in the current session. – Kermit Dec 15 '22 at 15:32
  • Alternatively you can use `proc contents` instead of the SAS view. Edited my answer. – Kermit Dec 15 '22 at 15:38
  • Kermit, it works, thank you I gave best answer! :) – dingaro Dec 15 '22 at 23:44