SAS macro to import multiple Excel sheets, clean invalid data, etc...

[Peter_C]

the demo code using a data step (mostly comments) follows[pre]
%let path= ~ ; * unix ;
%let path= !temp ; *windows ;
* CREATE A DATA FILE ;
data _null_ ;
file "&path/vo.sheet1.csv" ;
input ; list;
put _infile_;
CARDS;
Code,_1_Jan_95,_1_Feb_95........_1_Mar_11,_1_Apr_11
ABC,534,353,353,555
CDF,445,554,252,252
GHI,$$ERRORS,556,335,552
;

data datastream( keep= code date value ) ;
length code $8 date value 8 ;
format date monyy7. ;
infile "&path/vo.sheet1.csv" DSD DLM=',' column=col ;
******** column=col defines COL as a variable to hold the position
on the line after each input statement executes.
By default, the next input statement will read at this position
;
IF _N_ = 1 THEN DO ; * 1st time through, get start date from heading line ;
INPUT dummy $ DATE1 :$20. ;
list ; *echo line to log ;
DATE0 + input( compress( date1,'_'), ?? date11. ) ;
* this form of statement normally defines an accumulator
which are retained.
Here we remove "_" characters from the date-style column
header to make it suitable for the DATE. informat which
will provide a date that corresponds to the first value.
Thereafter columns are monthly - with dates which more easily
derived than "loaded" ;
if not date0 then stop ;
delete ; * having no other use for this line ;
end ;
********************** now read the data ;
date= date0 ; **** date of first value column ;
* start reading a line with the CODE column ;
input @1 code @ ;
do until( colO >= col ) ; * stop looking once at end of line;
colO = col ;
* input moves the column pointer COL, until end-of-line ;
INPUT value ?? @@ ;
* ?? ignores invalid data like $$ERROR
returning just missing value = .
@@ holds the line until next data step iteration ;
if value then do ;
* having found some more data, release a row of code,date and value ;
output ;
end ;
date = intnx( 'month', date, 1 ) ; * adjust date for next months column ;
end ;
* this step iterates once for each line on the input file ;
run ;

[smilingmelbourne]

Thank you so much for the help. I will definitely learn a lot from your codes. Best regards

[alexOU]

Hey. I have similar problems. But instead of large number of sheets, I have about ten thousand files. What can I do?
Plz help me with it.

[LarryWorley]

Alex,

Yuo get a list of file names to iterate over, you can use a filename pipe ..... to get a list of files.

Then iterate over Peter's code above to read the files if they are spreadsheets.  If not spreadsheets, then you might have to build your own code to handle reading the data.

I am attaching a paper I presented a couple of years ago which addresses this problem.

Larry

[LarryWorley]

I meant to add a liitle sample code.

Assuming windows OS, this code will be a data set with list of filenames in a directory

filename my_files pipe 'dir /b c:\my_directory' ;

data file_list ;

  infile my_files ;

  input file_name ;

run ;

[alexOU]

Thank you Larry