Import row2 as variable names from excel sheet

[sri1]

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!

[Tom]

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;

[sri1]

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;

[Patrick]

Sheet and Range are exclusive. Try: 

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

[Tom · Accepted solution]

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:';