Thursday, February 7, 2013

SAS: Reading the directory

I have to import a file which will be created everyday.  IT gets moved to the server automatically by another process.  It has date and time in the filename.  I won't know the time.  How can I import this?  Well, I need to determine the full filename, and to do so I will need to query the directory.

Here's a link to the article which I used:  http://support.sas.com/kb/41/880.html

Here's a subset of my code (which must run on Windows and Linux):

* Get todays date as a macro variable in MMDDYYYY format;

data _null_;
today = input("&SYSDATE9", anydtdte10.);
mmddyyyy = put(today, mmddyyn8.);
call symput("MMDDYYYY", mmddyyyy);
run;

%put &MMDDYYYY;

%macro op;
%if (&SYSSCP = WIN) %then %do;
    filename DIRLIST pipe "dir &SOURCE_LIB.&STARTS_WITH.&MMDDYYYY.*.csv " ;
%end;
%else %do;
    filename DIRLIST pipe "ls &SOURCE_LIB.&STARTS_WITH.&MMDDYYYY.*.csv" ;
%end;
%mend;

%op;

* I do not know full filename.  Has mmddyyyy followed by a six digit. ;
* number, followed by .csv.  I need to isolate that six digit number. ;
* See http://support.sas.com/kb/41/880.html ;
data dirlist ;                                              
infile dirlist lrecl=200 truncover;                         
input line $200.; 
where_is_dot_csv = index(line, ".csv");
if (where_is_dot_csv = 0) then delete;
where_is_date = index(line, "&MMDDYYYY");
number = substr(line, (where_is_date + 8), where_is_dot_csv - (where_is_date + 8));
put number=;
call symput("NUMBER", trim(left(number)));
run;

%let FULLNAME = &SOURCE_LIB.&STARTS_WITH.&MMDDYYYY.&NUMBER..csv;

proc import datafile="&FULLNAME"
    out=Work.Imported
    dbms=csv
    replace;
    getnames=yes
    ;
run;



No comments:

Post a Comment