topic Re: Use SET statement to concatenate the data of the 3 excel sheets in New SAS User

Use SET statement to concatenate the data of the 3 excel sheets

YangYY — Mon, 30 Sep 2019 02:12:12 GMT

I am asked to use set statement to concatenate the data of the 3 execl sheets into a SAS data set. And use INDSNAME option in SET statement to record which data is from which worksheet.

But I don't know how. My data has been uploaded. Anyone could help?

Thank you!!

Re: Use SET statement to concatenate the data of the 3 excel sheets

SASKiwi — Sat, 28 Sep 2019 05:20:58 GMT

Something like this should do it:

libname oil excel "oil_countries.xlsx";

data oil;
  set oil.oil_production
      oil.oil_consumption
      oil.oil_reserves
      indsname = sheet_name
     ;
run;

Re: Use SET statement to concatenate the data of the 3 excel sheets

YangYY — Mon, 30 Sep 2019 02:21:50 GMT

Thank you for your reply!

But the log file shows error.

My code is

libname assign03 excel "oil_countries.xlsx";

data oil;
set assign03.oil_production
assign03.oil_consumption
assign03.oil_reserves
indsname = sheet_name
;
run;

Log says

71 libname assign03 excel "oil_countries.xlsx";

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

73 data oil;

74 set assign03.oil_production

75 assign03.oil_consumption

76 assign03.oil_reserves

77 indsname = sheet_name

78 ;

ERROR: Libref ASSIGN03 is not assigned.

79 run;

Re: Use SET statement to concatenate the data of the 3 excel sheets

SASKiwi — Sat, 19 Oct 2019 23:01:59 GMT

@YangYY - Check to see if SAS/ACCESS to PC Files is installed and licensed:

proc product_status;
run;

proc setinit;
run;

Re: Use SET statement to concatenate the data of the 3 excel sheets

Tom — Sun, 20 Oct 2019 14:43:30 GMT

The EXCEL engine will only work if you are running on WIndows.

Try the XLSX engine instead.