CODEWITHSUNDEEP
sas
maniA
maniA

Reputation: 1457

read the name of all files in a given directory and find the pattern using SAS

I have the following task in SAS. Ggiven is an Excel file which I imported in SAS. In one column "looking_date" a date occurs I am interested in.

enter image description here

From the other hand there is a directory ("C:\myWork\") containing many files. enter image description here

The name of every file in this directory contains a date and another part which could be always different.

SO I try it in a algorithm form:

  1. look for every date in column "looking_date"
  2. check in directory "C:\myWork\" (kind of pattern matching) if the date from "looking_date" occurs in the name of one file then import the file with giving the date as the name

I have no idea how can I implement both steps above. At first I thought I read the name of all files from this directory, however I have still the problem with pattern the name! I am grateful for every hint just to be able to begin

Upvotes: 1

Views: 3739

Answers (1)

Robert Soszyński
Robert Soszyński

Reputation: 1188

To read a directory on the operating system you can use data step like this:

%let path_to_catalog=C:\myWork;

data files;
    /* define a directory */
    rc = filename("mydir","&path.");
    /* open the directory */
    did = dopen("mydir");
    /* check if it's opened */
    if did > 0 then do;
        /* itereate over objects in the directory */
        do n = 1 to dnum(did);
            /* read name of file */
            filename = dread(did, n);
            /* output files with *.DAT extension */
            if scan(upcase(filename), -1, '.') = 'DAT' then 
                output;
        end;
    end;
    /* close the directory */
    rc = dclose(did);

    drop rc did;
run;

read dates from an excel file

libname exc_lib pcfiles "path_to_execel";
proc sql;
    select distinct input(looking_date, ddmmyy8.)
    into :dates separated by ','
    from exc_lib.'sheet1$'n;
quit;

check if a file contains date from an excel file

data result;
    set files;
    date = input(substr(filename, 1, 8), ddmmyy8.);
    if date in (&dates) then output;
run;

Upvotes: 2

Related Questions