topic Import only specified rows columns from excel sheet in SAS Procedures

Import only specified rows columns from excel sheet

wfiona020 — Wed, 09 Nov 2022 16:14:21 GMT

if I have an excel file looks like this.

I use proc import detafile=.......... out=projoct_data;

and have a table like this:

How could I fix it?

Moreover, I want to row 2 in excel to become the name of data in the sas table, not F6,7,8,9...

Thanks

Re: Import only specified rows columns from excel sheet

Tom — Wed, 09 Nov 2022 16:46:06 GMT

Show the actual CODE you used to read the excel file.

Do you actually have an XLSX file? Or did you accidentally let Excel open a CSV file?

If you have an XLSX file then use the RANGE statement to tell it start in cell A2 instead of A1.

 range='$A2:';

Here is example:

%let fname = %sysfunc(pathname(work))/class.xlsx;

proc export data=sashelp.class file="&fname" dbms=xlsx replace;
run;

proc import file="&fname" dbms=xlsx out=test1 replace;
run;

proc import file="&fname" dbms=xlsx out=test2 replace;
  range='$A2:';
  getnames=NO;
run;

proc compare data=test1 compare=test2;
  var name sex age height weight;
  with a b c d e;
run;

If you have a CSV file then skip the PROC IMPORT and write your own data step to read it. Use the FIRSTOBS=3 option on the INFILE statement to skip both header lines.

Re: Import only specified rows columns from excel sheet

wfiona020 — Wed, 09 Nov 2022 17:08:58 GMT

It’s xlsx , not csv.
How can I change name too?

Re: Import only specified rows columns from excel sheet

Reeza — Wed, 09 Nov 2022 17:23:02 GMT

To get the names, use GETNAMES=YES or leave that option out and just specify the range starts at A2. If you know the ending of the range of data and this is a one time import it's sometimes useful to specify the end of the range, ie A2:J28