04-23-2018 01:31 PM
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,
04-23-2018 01:34 PM
04-23-2018 02:44 PM
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 */
select dd from days where dd not in (select dd from mm_list);
To check a formated name of members use either SUBSTR() or %LIKE or any other text functions.
04-23-2018 07:26 PM
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.
04-24-2018 01:04 AM
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.
04-23-2018 03:20 PM
@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.