topic Re: Importing multiple excel files from the PC Files Server in SAS Enterprise Guide

Importing multiple excel files from the PC Files Server

kdp — Mon, 06 Jun 2011 17:06:52 GMT

Hi,

I am trying to import multiple Excel files that are in a folder on the PC Files Server into EG 4.3.

I have used the libname and data step before to import just one file; however I am not sure how to tweak this to make it work for multiple files especially since the file names and # of files could be different every time.

I have also used the infile statement with the *.* option in a data step before to import multiple files, but the files were already on the SAS server.

Anybody know of a way to do this?

kdp

Re: Importing multiple excel files from the PC Files Server

tbatliner — Wed, 08 Jun 2011 14:27:56 GMT

Hi,

we are doing this with macros, similar to the code below:

[pre]
%macro import;

data files(keep=Filename);
rc=filename("mydir","C:\PathToMyFiles");
did=dopen("mydir");
if did GT 0 then do;
memcount=dnum(did);
if memcount gt 0 then do i=1 to memcount;
Filename=dread(did,i);
/* subsetting the list */
if substr(upcase(Filename),1,3)='BLA' and INDEX(upcase(Filename) ,'.XLS') then output;
end;
end;
run;

data files;
set files;
call symput('numcopyf',_N_);
run;
%put Files to read: &numcopyf;

%if &numcopyf gt 0 %then %do;
%do i=1 %to &numcopyf;
data _null_;
set files;
if _N_=&i then do;
fn=filename;
call symput('f',fn);
end;
run;
%readinexcelfile(&f);
%end;
%end;

%mend import;
[/pre]

In the %readinexcelfile macro you'll have to put your code for importing the data.

hth, Thomas

Re: Importing multiple excel files from the PC Files Server

kdp — Wed, 08 Jun 2011 16:37:12 GMT

Hi Thomas,

I tried your code and it didn't seem to work. The first data step results in 0 observations even though there are files in the directory.

I should mention that I am running EG 4.3 on the server, so maybe that's why it can't recognize the path.

Is there a way to map the PC Files Server as a server in the Server List in EG?

Thanks for the code though, I will use it in some other application.

Thanks,
Ketan

Re: Importing multiple excel files from the PC Files Server

tbatliner — Thu, 09 Jun 2011 07:00:01 GMT

Hi Ketan,

asking the obvious, you did change the path in [pre] rc=filename("mydir","C:\PathToMyFiles"); [/pre], did you? 🙂

Afaik the PC Files SERVER isn't a classic server like a workspace server, therefore there is no way to display it in the EG server list. What would you want to achieve by doing this?

br, Thomas

Re: Importing multiple excel files from the PC Files Server

kdp — Thu, 09 Jun 2011 15:31:21 GMT

Hi Thomas,

Yup, I did change the path name. 🙂

In regards to mapping it in the EG server list...I was just thinking about other ways I could read the data in...just shooting in the dark at this point.

Do you run this code on PC SAS or server based SAS?

Thanks,
Ketan

Re: Importing multiple excel files from the PC Files Server

tbatliner — Thu, 09 Jun 2011 15:47:08 GMT

Hi,

we are using server based SAS with Win 64. Are you using IWA and the folder is on a different server than the workspace server? Because I just found out that that can result in problems. Does [pre] libname test "C:\PathToMyFiles"; [/pre] work for you in EG?

br Tom

Re: Importing multiple excel files from the PC Files Server

kdp — Thu, 09 Jun 2011 16:09:27 GMT

Not sure what IWA means, but the PC File Server is on a different server than all of our SAS servers.

I tried the libname you suggested (yes, I did change the path), but the log states that the library does not exist.

Thanks,
kdp

Re: Importing multiple excel files from the PC Files Server

tbatliner — Thu, 09 Jun 2011 16:25:34 GMT

IWA stands for Integrated Windows Authentication, meaning that you're using eg. smartcard's for authentification...

Well if EG can't find/read the folder you're trying to read files from than I guess you're having some sort of security restrictions in place. You'll need to resolve that first, PCFILES will only come afterwards...

br Thomas

Re: Importing multiple excel files from the PC Files Server

kdp — Thu, 09 Jun 2011 16:41:14 GMT

You are right! I'll try to engage SAS Support.

Thanks for all your help!!

Ketan