CODEWITHSUNDEEP
sharepointsas
rom
rom

Reputation: 81

Import excel files from sharepoint folders in SAS

I have googled it and also searched on this site and SAS communities but I haven't come across an answer to my problem. Some of the discussions were specific to SAS EG, some were specific to html files, and some used the data step.

I am trying to import all the excel files on a sharepoint location using proc import and I am using base SAS. Can anyone advise me on how to do this? Can I do this using base SAS and nothing else?

Upvotes: 2

Views: 4414

Answers (1)

DomPazz
DomPazz

Reputation: 12465

You can mount a Sharepoint location in Explorer. Use that path to create a FILENAME reference to the location. Then use a Data Step to get all the file names contained in the location. From there, filter them to .xls (or .xlsx) files. Finally, iterate over the file names, reading them in from the location.

NOTE: You must map the network drive through Windows Explorer and refer to that mapped drive in the FILENAME statement.

filename sp "y:\";

data files;
did = dopen("sp");
n = dnum(did);
format file $128.;
do i=1 to n;
      file = dread(did,i);
      if index(file,".xls") then
            output;
end;
rc = dclose(did);
run;

data _null_;
format name $8. cmd $200.;
set files;
name = scan(file,1,". ");

cmd = "proc import file='"||pathname("sp")||"\"||strip(file)||"' out="||name||" dbms=excel replace; run;";
call execute(cmd);
run;

Now if you want to read more than the first tab, then you need a way to query the file for the available tabs (possible with SAS ACCESS/PC Files but that's a different question).

Upvotes: 1

Related Questions