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

[student12345678]

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!!!

[Kurt_Bremser · Accepted solution]

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.

[student12345678]

Thanks a lot!!!

[andreas_lds]

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.

[Tom]

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