0

I have table in SAS like below:

TABLE 1

COL3  | COL2 | ...  | COLn
------|------|------|----
111   | ABC  | ...  | 12
222   | AAA  | ...  | 10.5
333   | app  | ...  | 5
...   | ...  | ...  | ...

And I have table in Excel file like below:

TABLE 2

GROUP| NAME  | DEFINITION
-----|-------|-------------
ABC  | COL1  | xxxxxxx
ABC  | COL2  | xxxxxxxxxx
BBB  | COL15 | xxxxxxxxx
...  | ...   | ...

And I need to remove from TABLE 1 (SAS table) variables which are listed in "NAME" column in TABLE 2 (Excel file).

So as a result i need something like below (using only example above but i have many more columns of course).

COL3  |  ... | COLn
------|------|----
111   | ...  | 12
222   | ...  | 10.5
333   | ...  | 5
...   | ...  | ...

How can I do that in SAS Enterprise Guide ?

Kermit
  • 3,112
  • 2
  • 10
  • 34
dingaro
  • 2,156
  • 9
  • 29

1 Answers1

1

Create sample dataset and .xlsx file

data have;
input col1-col5 ;
cards;
1 2 3 4 5
6 7 8 9 10
;
run;

enter image description here

Import the file

proc import datafile='/home/kermit/have.xlsx' 
        dbms=XLSX replace 
        out=have_xlsx(keep=NAME);
run;

Fill the cols macro variable with the list of distinct columns to drop from the name column

proc sql noprint;
    select distinct name into :cols separated by ' '
    from have_xlsx;
quit;

Drop the selected columns

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

As a result, only col3 is kept

col3
 3
 8

After your comment

There are two ways

  1. Play with the DKRICOND option before the data step. It will suppress the error message that would normally be generated by trying to drop a variable that does not exist and will let your data step execute (not recommended)
option DKRICOND=NOWARN;
data want;
    set have(drop=&cols.);
run;
option DKRICOND=ERROR;
  1. Adapt the current SQL query to only retrieve columns that exist in both tables
proc sql noprint;
    select distinct upcase(name) into :cols separated by ' '
    from have_xlsx
    where upcase(name) in 
    (select distinct upcase(name) from sashelp.vcolumn
        where upcase(memname) = 'HAVE');
quit;
Kermit
  • 3,112
  • 2
  • 10
  • 34
  • Kermit, it looks good, I gave you best answet, thank you :) – dingaro Dec 15 '22 at 11:07
  • Kermit, i used your code and i have error like that: ERROR: The variable COLxyz in the DROP, KEEP, or RENAME list has never been referenced. What can i do ? – dingaro Dec 15 '22 at 14:05
  • Probably column COLxyz does not exists in my TABL1 1 (SAS table) but is in Excel file, so how can I modify this code to remove variables from exxcel file in it exists in sas table ? – dingaro Dec 15 '22 at 14:08
  • 1
    Edited my answer. Please have a look. – Kermit Dec 15 '22 at 14:42
  • Thank you Kermit :), could you have a look on my second question if you have a time ? https://stackoverflow.com/questions/74813248/how-to-drop-columns-from-sas-table-ends-with-defined-suffixes-if-such-columns-e – dingaro Dec 15 '22 at 14:53