Desktop productivity for business analysts and programmers

Import multiple sheets form excel in SAS and append

Reply
Contributor
Posts: 22

Import multiple sheets form excel in SAS and append

Hi,

 

I am using SAS Enterprise guide and I want to import multiple sheets from excel to SAS. In excel first sheet has column headers but other sheets don’t have column headers.

 

Now I want to import all the sheets at the same time in SAS then append all in one append to the first dataset created form sheet1 as it has column headers.

 

I would appreciate your help.

 

I found something on the web so I tried using following code but library is not getting assigned.

 

LibName xlsLib ‘H:\Pneumonia.xls’;

 

I am getting error:

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Regular Contributor
Posts: 212

Re: Import multiple sheets form excel in SAS and append

Hi Anna,

 

There is a simple way using macro to determine the Sheet you need.

 

Do the following example do this:

 

%macro pim(sheet);
proc import out= payment
    datafile = 'E:\SAS DOC\project\Credit Banking.xls'
    dbms = Excel;
    sheet = "&sheet";
    getnames = yes;
run;
%mend piim;
%pim(Customer Acqusition);
%pim(Spend);
%pim(Repayment);

Hope this helps Smiley Happy

 

Att

 

Rodrigo Dartibali 

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Import multiple sheets form excel in SAS and append

Well, do you have the required SAS/ACCESS license?  Run proc setinit.  You can try the libname excel statement, described;

http://www2.sas.com/proceedings/sugi31/024-31.pdf

 

Its easy enough to work with.  What I would say however, is all your data the same on all the sheets, Excel is not a structured format, hence if you have mix of chars/numbers, specials, dates are always tricky, you are going to have difficulties.  Personally I would get the data in  proper data transfer format and have an agreement with the vendor of what is supplied.

Contributor
Posts: 22

Re: Import multiple sheets form excel in SAS and append

Thanks.

 

Please note only first sheet has variable names on the top and other sheets don’t. So when I import each sheet separate, 2/3/4 sheets will have variables as A, B, C after they get imported in SAS…if I do:

 

getnames = no;

 

And then append is a problem.

 

on the other hand if I have getnames = yes; first row will be displayed as varaible names..that's also a problem in appending all these sheets together in SAS.

 

Regular Contributor
Posts: 212

Re: Import multiple sheets form excel in SAS and append

Anna, So for the sheet that does not contain the column names try this:

 

%macro pim(sheet,start,namerow);
proc import out= payment
    datafile = 'E:\SAS DOC\project\Credit Banking.xls'
    dbms = Excel;
    startrow=&start;
    namerow=&namerow.;
    sheet = "&sheet";
    getnames = yes;
run;
%mend piim;
%pim(Customer Acqusition,2,3);
%pim(Spend,1,1);
%pim(Repayment,1,1);

Att

New Contributor
Posts: 3

Re: Import multiple sheets form excel in SAS and append

Outstanding! Thank you very much!

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Import multiple sheets form excel in SAS and append

How is this outstanding, the thread is from 2015, please avoid necro'ing posts.

New Contributor
Posts: 3

Re: Import multiple sheets form excel in SAS and append

What is "necro'ing"? I'm sorry, I'm new here. I learned from this code what I needed to do and it worked. It was very helpful and to me, "Outstanding". What's the issue? I'm sorry, I do not understand.

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Import multiple sheets form excel in SAS and append

To necro a post is to raise it from the dead.  This thread is from 2015, it is very old.  Posting again to an old thread raises it back up to the forefront, I for instance know this because I get automatic emails from posts I have contributed to, so as you can imagine, if people starting posting in all old posts, my email would collapse.  You can show your apreciation to posts by clicking the Like button below the post.

Also note that old posts my not use current thinking/technology.  For instance if you are using an XLSX file, and have SAS 9.4 then you can far simpler access data by using the libname excel engine:

http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Ask a Question
Discussion stats
  • 8 replies
  • 497 views
  • 0 likes
  • 4 in conversation