topic Re: Import multiple sheets form excel in SAS and append in SAS Enterprise Guide

Import multiple sheets form excel in SAS and append

AnnaShami — Tue, 27 Oct 2015 13:21:29 GMT

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.

Re: Import multiple sheets form excel in SAS and append

DartRodrigo — Tue, 27 Oct 2015 13:28:09 GMT

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

Re: Import multiple sheets form excel in SAS and append

RW9 — Tue, 27 Oct 2015 13:37:32 GMT

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.

Re: Import multiple sheets form excel in SAS and append

AnnaShami — Tue, 27 Oct 2015 13:48:32 GMT

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.

Re: Import multiple sheets form excel in SAS and append

DartRodrigo — Tue, 27 Oct 2015 14:18:16 GMT

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

Re: Import multiple sheets form excel in SAS and append

ira_jacobson_delta_com — Thu, 09 Mar 2017 18:31:38 GMT

Outstanding! Thank you very much!

Re: Import multiple sheets form excel in SAS and append

RW9 — Thu, 09 Mar 2017 19:33:51 GMT

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

Re: Import multiple sheets form excel in SAS and append

ira_jacobson_delta_com — Thu, 09 Mar 2017 19:38:55 GMT

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.

Re: Import multiple sheets form excel in SAS and append

RW9 — Thu, 09 Mar 2017 19:44:29 GMT

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/