topic Re: importing data in SAS Programming

importing data

katkarparam — Mon, 04 Jun 2018 12:15:57 GMT

Read multiple file at a time?

I have a piece of code. But it will read sheet one by one. i mean i have to call the macro with my particular sheet name.

is there another way i can read all sheet at a time and create one dataset?

%Macro Due_Cal(sheet);

Proc import out= Due_date

Datafile = 'E:\Bank Data Model\project bank\Loan_Congo_Banking.xls'

Dbms = Excel;

Sheet = "&sheet";

Getnames = yes;

run;

%mend Due_Cal;

% Due_Cal (Kinshasa);

Shmuel — Mon, 04 Jun 2018 12:32:14 GMT

Are all the sheets in the excel file the same format?

Can you save your .xls file as .xlsx file?

What sas version do you use?

If your input was a .xlsx file and all your sheets the same format then you could use a code like:

libname myxl xlsx '<path and name>.xlsx';  

data want;
  set myxl.<sheet1_name>
      myxl.<sheet2_name>
     ...
 ; run;

katkarparam — Mon, 04 Jun 2018 12:35:40 GMT

sas 9.2 .

windows platform.

microsoft excel 10.

i will try your methd and let u know sir..

Thnaks for the valuable time...

RW9 — Mon, 04 Jun 2018 12:40:30 GMT

Great advice, use the xlsx engine, but you must have SAS 9.4. To note, you can simply copy so you don't need to type in each one:

libname myxl xlsx '<path and name>.xlsx'; 

proc copy in=myxl out=work;
run;

libname myxl clear;

Don't forget to clear the file ref afterwards.

katkarparam — Mon, 04 Jun 2018 13:21:02 GMT

thanks...it works..

katkarparam — Mon, 04 Jun 2018 13:22:05 GMT

thank u..sir..