topic Re: Drop coulmns in excel when proc import in SAS Programming

Drop coulmns in excel when proc import

raja777pharma — Thu, 13 Aug 2020 08:57:59 GMT

Hello ,

I have tried proc import with getnames=Yes|No , but couldn't find a right way do it below scenario.

When Excel column name ( A,B,C,D -n) and variables names (A1,B1,C1,D1-n1) are same then those columns are need to drop in sas data set output

Example: In Below screen shot of excel sheet there are five columns (A-E) and i want in output sas data set only columns A,B and C .

output data set variables should be SUBJID,STUDY and DATE only

this example of excel sheet, for my data excel sheet have more columns and randomly the varibles names are same as excel columns names so need to drop the same columns when proc import

Thank you,

Raj

Re: Drop coulmns in excel when proc import

andreas_lds — Thu, 13 Aug 2020 09:40:06 GMT

Have you tried using the keep-option:

proc import .... data=work.import(keep=SubjId Study Date) ...

Re: Drop coulmns in excel when proc import

Kurt_Bremser — Thu, 13 Aug 2020 09:57:47 GMT

Create a reference table that connects variable position in a dataset with the respective default Excel column name:

data excel_cols;
length name $32;
varnum = 0;
do i = 65 to 65 + 25;
  varnum + 1;
  name = byte(i);
  output;
end;
do i = 65 to 65 + 25;
  do j = 65 to 65 + 25;
    varnum + 1;
    name = byte(i) !! byte(j);
    output;
  end;
end;
run;

This will give you the names from A to ZZ (more than 700 columns, should suffice).

Then you join this with the metadata of your imported table:

proc sql noprint;
select d.name
  into :drop_cols separated by " "
  from dictionary.columns d right join excel_cols c
  on d.name = c.name and d.varnum = c.varnum
  where d.libname = "WORK" and d.memname="TAB1"
;
quit;

%put &drop_cols;

The macro variable will contain the column names of the typical "unnamed" columns in the spreadsheet. You can use it in a DROP statement or DROP= dataset option.

I tested this with an import of an export of sashelp.class where I removed the headers for weight and height in the spreadsheet (see attachment), and it gave me

D E

as result, as it should be.