02-17-2013 09:13 PM
I encounter some difficulties in processing a bunch of data. I would like to import all files in the folder into SAS. Could anyone help me on this. I have attached four files: the first three files are examples I have to import and the last file is the report I would like to generate.
There are some steps I have to do:
1. Only include information of Institutional Ownership, starting from the observation 30th.
=> I think of using FIRSTOBS function in PROC IMPORT
Also, the last paragraph (after institutional ownership) should be excluded
=> I think of using MISSING function of more than two cells then delete
2. Need to rename all the variable => since when importing in SAS, it will automatically changes the name to VAR
3. To create the PERIOD column in the FINAL_RESULT
=> I use the PROC TRANSPOSE => need to reformat the DATE in POSITION column
4. Create the COMPANY NAME => don't have any idea yet on this.
Can anyone help me on this? Any suggestion is greatly appreciated! Thanks
02-18-2013 11:32 AM
Here is some code to get all the excel filenames in a folder in a dataset. The readin might be a little different depending on whether this is a one time task or has to be repeated over and over again.
Data xlsx_file ;
length file_name $200 ;
rc = filename("dir","FOLDER PATH HERE");
d = dopen("dir");
n = dnum(d);
do i = 1 to n;
file_name = dread(d,i);
if lowcase(scan(file_name,2,'.')) = 'xls' then output;
02-18-2013 09:18 PM
This question has been asked before. Go to the main SAS Communities page
and search for
You'll find an number of approaches.
Also, this particular forum is intended for help with the forum itself. Your future posts might get more answers if they are in the data step or procedures forums (depending on the question).