topic Re: Import row2 as variable names from excel sheet in SAS Programming

Import row2 as variable names from excel sheet

sri1 — Thu, 21 Nov 2024 16:53:25 GMT

I have an excel sheet like below , I want column headers in row2 as variable names of sas dataset from sheet1 .Is there anyway I can do in proc import step ?

+-------+-----------------+----------------+ | A | B | C | +-------+-----------------+----------------+ | SEX | NAME | AGEC | +-------+-----------------+----------------+ | M | Alfred | 14 | | F | Alice | 13 |

I have tried something like this but didn't work

proc import datafile="C:\in.xlsx"
out=want
dbms=xlsx
replace;
range='$A2: ';
sheet='sheet1';
run;

Appreciate your response!

Re: Import row2 as variable names from excel sheet

Tom — Thu, 21 Nov 2024 17:45:57 GMT

What you posted should work. Did you get errors in the LOG?

Try this example:

data test;
  input (A B C) ($);
cards;
A B C
Name Sex Age
Alfred M 14
Alice F 13
;

filename xlsx temp;
proc export data=test file=xlsx dbms=xlsx replace;
  putnames=no;
run;

proc import dbms=xlsx file=xlsx out=test1 replace;
run;

proc import dbms=xlsx file=xlsx out=test2 replace;
  range='$A2:';
run;

proc print data=test1;
  title 'test1';
run;
proc print data=test2;
  title 'test2';
run;

Re: Import row2 as variable names from excel sheet

sri1 — Fri, 22 Nov 2024 10:02:06 GMT

Thanks for your response. There are no errors in LOG.

The example posted works but when I changed the path as below & specifying sheet name , it's not giving desired output. I have to specify sheet name as I have multiple sheets in my excel file.

data test;
  input (A B C) ($);
cards;
A B C
Name Sex Age
Alfred M 14
Alice F 13
;

filename xlsx 'C:\in\test1.xlsx';
proc export data=test file=xlsx dbms=xlsx replace;
  putnames=no;
run;

proc import datafile=test1 file=xlsx dbms=xlsx out=test2 replace;
  sheet='Sheet1';
  range='$A2:';
run;

proc print data=test2;
  title 'test2';
run;

Re: Import row2 as variable names from excel sheet

Patrick — Fri, 22 Nov 2024 10:15:43 GMT

Sheet and Range are exclusive. Try:

proc import 
  file='C:\in\test1.xlsx' 
  dbms=xlsx 
  out=test2 
  replace;
  range='Sheet1$A2:';
run;

Re: Import row2 as variable names from excel sheet

Tom — Fri, 22 Nov 2024 12:57:53 GMT

Don't use SHEET. If you want to read from other than the FIRST sheet in the workbook then include the sheetname in the RANGE value.

  range='sheet1$A2:';