topic Re: Importing an xlsx sheet without headers? in SAS Programming

Importing an xlsx sheet without headers?

GreyBlack — Sun, 04 Dec 2022 23:56:57 GMT

Good evening, everyone!

So I have an xlsx file that I'm trying to import into Excel that doesn't have any column names within the actual file. I'm able to use the proc import to get the data out from the file, but it reads row 1 as the names of the columns.

Does anyone have a way to name the columns of an xlsx data set as you're importing?

For reference, my code right now is:

Proc import out=data1 file='c:\filename.xlsx' dbms=xlsx;
Sheet="sheet1";
Getnames=no;
Run;

Re: Importing an xlsx sheet without headers?

Tom — Mon, 05 Dec 2022 00:10:27 GMT

I do not understand what you are asking since the code you showed does import an XLSX sheet that does not have a header row.

Example:

filename xlsx temp;
proc export dbms=xlsx file=xlsx data=sashelp.class;
  putnames=no;
run;

proc import dbms=xlsx file=xlsx out=want replace;
  getnames=no;
run;

If you know the names you want to use include them in the output dataset name using the RENAME= dataset option.

proc import dbms=xlsx file=xlsx out=class(rename=(a=name b=sex c=age d=height e=weight)) replace;
  getnames=no;
run;

Re: Importing an xlsx sheet without headers?

GreyBlack — Mon, 05 Dec 2022 00:07:41 GMT

Perhaps I should rephrase; I'm hoping to add headers after performing the
import, but I'm not clear on how to do that.

Re: Importing an xlsx sheet without headers?

Tom — Mon, 05 Dec 2022 00:12:22 GMT

@GreyBlack wrote:
Perhaps I should rephrase; I'm hoping to add headers after performing the
import, but I'm not clear on how to do that.

That makes even less sense. Once the file is imported it is a dataset. Datasets do not have headers. The variables in a dataset have names. If you know the names you want to use for the variables you can include that in the code using the RENAME= dataset option.

proc import dbms=xlsx file=xlsx out=class(rename=(a=name b=sex c=age d=height e=weight)) replace;
  getnames=no;
run;

Re: Importing an xlsx sheet without headers?

Kurt_Bremser — Mon, 05 Dec 2022 08:13:32 GMT

The usual remedy: save to a .csv file and read that with a DATA step, where you set all names, types and attributes without relying on the guesses of PROC IMPORT.

Re: Importing an xlsx sheet without headers?

Patrick — Mon, 05 Dec 2022 08:24:10 GMT

@GreyBlack wrote:
Perhaps I should rephrase; I'm hoping to add headers after performing the
import, but I'm not clear on how to do that.

Your variable names will be A, B, C,...

If you need to rename them then use either a rename statement within a SAS datastep or if you don't need to process the data then use Proc Datasets.

Below some sample code using Proc Datasets.

data work.imported_from_excel;
  a=1;
  b='a';
  c=99;
run;

proc datasets lib=work nolist;
  modify imported_from_excel;
    rename 
      a=myvar
      b=some_other_name
      c=new_name
      ;
  run;
quit;