topic Import mutiple (8500) Excel Files from one folder to SAS OnDemand for Academics in SAS Studio

Import mutiple (8500) Excel Files from one folder to SAS OnDemand for Academics

student12345678 — Wed, 07 Apr 2021 11:02:44 GMT

Hello,

I'm struggling with importing multiple Excel Files into SAS onDemand for Academics.

I'm using the Robintrack dataset and havelaready unpacked it on my PC. Now I have 8597 Excel files in my PC folder, which are all named according to the respective stock.

Can someone help me to write an Macro to import all Excel files into one SAS table with an additional Variable which has the stockname in it?
To import one file is not a problem but I'm not able to import all files at once in a macro, too save them all in one SAS table and to give assigne them a new variable which has the corresponding stock name.

Thanks a lot!!!

Re: Import mutiple (8500) Excel Files from one folder to SAS OnDemand for Academics

Kurt_Bremser — Wed, 07 Apr 2021 11:56:45 GMT

You can use the upload functionality of SAS Studio to upload the files in batches (I managed A to F in one swoop, but it takes TIME!), after which you can run a single data step to read the files:

data robin;
length fname $200 listing $4;
infile '$HOME/robin/*.csv' filename=fname dlm="," dsd;
input@;
if index(_infile_,"timestamp") = 0;
input timestamp :e8601dt19. listed;
format timestamp e8601dt19.;
listing = scan(scan(fname,-1,"/"),1,".");
run;

I put all the csv files in a folder called robin located directly in my Home on SAS ODA.

Re: Import mutiple (8500) Excel Files from one folder to SAS OnDemand for Academics

andreas_lds — Wed, 07 Apr 2021 12:23:42 GMT

Was already on the way to lose consciousness, but then i realized that you don't have excel-files, but csv-files. Do yourself a favor and switch the default application used to open csv-files to a text-editor, that way you will see what's really in the files and not what Excel things you should see.

Re: Import mutiple (8500) Excel Files from one folder to SAS OnDemand for Academics

student12345678 — Wed, 07 Apr 2021 18:24:51 GMT

Thanks a lot!!!

Re: Import mutiple (8500) Excel Files from one folder to SAS OnDemand for Academics

Tom — Wed, 07 Apr 2021 19:55:30 GMT

You can also tell Windows that you want it to show you the real filenames instead of stripping off the extension.