Hi
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!
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:';
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;
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;
Sheet and Range are exclusive. Try:
proc import
file='C:\in\test1.xlsx'
dbms=xlsx
out=test2
replace;
range='Sheet1$A2:';
run;
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:';
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.