Import multiple sheets form excel in SAS and append

[AnnaShami]

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.

[DartRodrigo]

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 

Att

Rodrigo Dartibali 

[RW9]

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.

[AnnaShami]

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.

[DartRodrigo]

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

[ira_jacobson_delta_com]

Outstanding! Thank you very much!

[RW9]

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

[ira_jacobson_delta_com]

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.

[RW9]

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/