evaluate directory for daily files

[Elliott]

Is there an easy way with a sas query to evaluate the contents for a directory for a daily file for the previous month.

I have a monthly program the will roll up all the daily files.  But before I run that code I want a quick query to check that there is a daily file for each day of the previous month.  I have been doing it manually and of course I missed a day where the daily files did not get created, so I thought there should be a query that could do this quickly and easily.  Is there?

Thanks in advance,

Elliott

[Reeza]

So you have a folder and you expect files to be listed in the folder, one for every day of the month? Is it Unix or Windows? Do you have XCMD? Yes, there are queries that could be easily run, but you need to provide more details. For example how do you know a file exists for a particular day?

[Shmuel]

Assuming all your daily datasets have same format name,

you can check the listed members of a library in sashelp.vtable:

%let mm2check = 201804;   /* yyyymm */
data mm_list;
  set sashelp.vtable;
        where libname = <lib name> and
        memname <check name that fit the format> and
        put(crdate, yymmddn6.) = "&mm2check";
     dd = day(crdate);
     wd = weekday(crdate);  
run;

/* create skilton of 31 days */
data days; do dd=1 to 31; output; end; run;

/* list missing days */
proc sql;
   select dd from days where dd not in (select dd from mm_list);
quit;

To check a formated name of members use either SUBSTR() or %LIKE or any other text functions.

[Elliott]

There is a daily process reads in a .txt files and create the daily datasets.  Sometimes the process may fail and the datasets do not get created.  Usually a weekend server outage.   This is on SAS Grid Linux environment.  the data set name is us_call_level_20180401.sasbdat7.  One for each day of the month.  During the first week of the next month I will run a program to combine all the days of data to one monthly dataset called us_call_level_mnth_201804.  Before I do that I need to make sure no days are missing.  The query that evaluates for a daily file each month needs to account for how many days are in the month being evaluated.  For the roll up of March there needs to be a file for days 1 - 31, for the April roll up needs to know that there is only days 1 - 30.  Or if it is a leap year that there should be 29 days in Feb.    So if a day is missing I would need to throw an error and provide the date that is missing so an adhoc process can be run to create the missing day(s) datasets.  I would like to automate this process so I don't have to do the monthly review and roll up adhoc.  Then the process could be put in our scheduler.  If I had a query that could check for a dataset for each day of the month being evaluated, then with the successful completion that would trigger the monthly roll up program.  Does that makes sense?

Thanks to everyone trying to help me figure this out.

[Shmuel]

I understand that you know how to calculate the number of days in a month.

I would do it by: 

%let run_on = 201804;
data _null_;
    days = intnx(input(cat("&run_on" , "01"),yymmdd8.),1) - 1;
run;

then use in same or other step a loop to chrck does a file exist using exist() function.

Maybe even check the NOBS of the dataset and is it reasonable.

Finally assign return code to SYSCC where 0=OK, 1 to 4 means warning, 5 or more means error.

Use it in scheduler to decide either to continue or stop.

[Reeza]

Why wouldn't your process alert you to the fact that it failed. I think you should fix the source here, not account for the issue.

[Reeza]

I assumed these were text files...if they're SAS data sets its a different question.

[Shmuel]

@Reeza you are absolutely right.

If it is a directory of text files it can be done using:

-  dopen() function to open a directory

-  check filenames to fit the format and maybe to month and date

if that not enough:

-  fopen() function to open a filename

-  fattr() function to check file attributes including date created.