topic Re: Create SAS dataset based on the EXCEL data where the data starts from specific cell value in SAS Programming

Create SAS dataset based on the EXCEL data where the data starts from specific cell value

possible — Thu, 15 Jun 2023 15:48:08 GMT

I have a data from excel and trying to create the SAS dataset to comparison.

As shown in the below, my data starts from cell B5 and B4 is my header. With the below i was able to get the data from row 5 but how can i remove the empty column and assign the header with B4 values.

/*excel file*/
  A   B         C       D       E        F 
1
2
3
4     Subject  name   address   city     state
5     101      mary   324 jas   NN        mm
6     102      second 85  hsd   mkas      ewe
7     Subject  name   address   city     state
8     222      tird   67 hhsbd  yes       vgdsd
9     558      sdhd   792 djsh  kues      shdj


%macro impo(path=, in=, out=);
%let sfile="\file_path\&in..xlsx";

proc import
datafile=&path
out=&out.
dbms=xlsx replace;
sheet=&sheetbou;
getnames=yes;
datarow=5;
run;

%mend;
%impo(path=&sfile, in=&inbou, out=&prgm);

Re: Create SAS dataset based on the EXCEL data where the data starts from specific cell value

JOL — Thu, 15 Jun 2023 16:39:00 GMT

If you specify the input RANGE including the header, and set GETNAMES = YES, it will work.

Re: Create SAS dataset based on the EXCEL data where the data starts from specific cell value

Reeza — Thu, 15 Jun 2023 16:56:16 GMT

Not sure if this works in import but if you leave 0 as the end of the range, it will automatically take the full range.

Worth a try at least.