Importing multiple excel files from the PC Files Server

[kdp]

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

[tbatliner]

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

[kdp]

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

[tbatliner]

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

[kdp]

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

[tbatliner]

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

[kdp]

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

[tbatliner]

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

[kdp]

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

Thanks for all your help!!

Ketan