Drop coulmns in excel when proc import

[raja777pharma]

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

[andreas_lds]

Have you tried using the keep-option:

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

[Kurt_Bremser · Accepted solution]

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.