DATA Step, Macro, Functions and more

importing data

Reply
Occasional Contributor
Posts: 19

importing data

[ Edited ]

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);

Trusted Advisor
Posts: 1,831

Re: importing data

[ Edited ]
Posted in reply to katkarparam

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;

 

Occasional Contributor
Posts: 19

Re: importing data

sas 9.2 .

windows platform.

microsoft excel 10.

 

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

Thnaks for the valuable time...

Super User
Super User
Posts: 9,407

Re: importing data

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.

Occasional Contributor
Posts: 19

Re: importing data

thanks...it works..
Occasional Contributor
Posts: 19

Re: importing data

thank u..sir..
Ask a Question
Discussion stats
  • 5 replies
  • 89 views
  • 0 likes
  • 3 in conversation