DATA Step, Macro, Functions and more

Do Loop Dates macro

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Do Loop Dates macro

Hi,

 

I'm just wondering if anyone could help me enhance my code by adding do loop in my code.

 

 

%let logpath = /sas/logs/;

%let logdate = %sysfunc(putn(%eval(%sysfunc(today())-1),yymmdd10.));

%logname=logfile_&logdate;

 

 

data work.logfile:

infile "&logpath.&logname..log" lrecl=1000

firstobs=2 truncover;

input 

       date $ 1-23

       message $ 24-400:

 

run;

 

The code above is extracting the log file yesterday and I have a code that appends it everyday to keep it in a text file. Can someone help me enhance the code above if for example even if I didn't trigger it for 1week when I run the code the days that aren't cover will run without manually setting the date?

 

The logic that I think of is using a macro that performs a do loop using the difference of the max date in the text file that I have created and today()-1 as the end date. 

 

 

Thanks.

Albert


Accepted Solutions
Solution
‎10-03-2017 09:27 PM
Super User
Posts: 10,530

Re: Do Loop Dates macro

You can extract the startdate into a macro variable with SQL:

proc sql noprint;
select max(date) into :startdate from have;
quit;

Then you can repeat your code in a macro:

 
%let logpath = /sas/logs/;

%macro repeat_import;
%do i = %eval(&startdate+1) %to %eval(%sysfunc(today())-1);
  %let logname=%sysfunc(putn(&i,yymmdd10.));
  %logname=logfile_&logdate;
 
  data work.logfile:
  infile "&logpath.&logname..log" lrecl=1000
  firstobs=2 truncover;
  input 
    date $ 1-23
    message $ 24-400
  :
  run;

  proc append data=logfile base=have;
  run;

%end;
%mend;
%repeat_import
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎10-03-2017 09:27 PM
Super User
Posts: 10,530

Re: Do Loop Dates macro

You can extract the startdate into a macro variable with SQL:

proc sql noprint;
select max(date) into :startdate from have;
quit;

Then you can repeat your code in a macro:

 
%let logpath = /sas/logs/;

%macro repeat_import;
%do i = %eval(&startdate+1) %to %eval(%sysfunc(today())-1);
  %let logname=%sysfunc(putn(&i,yymmdd10.));
  %logname=logfile_&logdate;
 
  data work.logfile:
  infile "&logpath.&logname..log" lrecl=1000
  firstobs=2 truncover;
  input 
    date $ 1-23
    message $ 24-400
  :
  run;

  proc append data=logfile base=have;
  run;

%end;
%mend;
%repeat_import
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 226

Re: Do Loop Dates macro

Idea:
1. Get distinct dates from the dataset containing all processed logs.
2. Get the files in the log-directory, use dataset from step 1 to filter the files.
3. Enhance the datastep you already have: read the files to process from the dataset created in step 2 and use infile's filevar option to read all new files in one step.

See the documentation for example code.
Super User
Super User
Posts: 9,802

Re: Do Loop Dates macro

So, you just want to append all text files in a directory, then get some output from them.  Why not:

data work.logs;
  length log_name date message $500;
  infile "&logpath.logfile_*.log" lrecl=1000 indsname=fn;
  input date $ 1-23 message $ 24-400;
  log_name=fn;
run;
 

This will read all .log files from the given path into one dataset with three columns - filename, date, and message.  You can then process that as you want.

Contributor
Posts: 43

Re: Do Loop Dates macro

Thank you Kurt. The code works fine.


-Albert
Contributor
Posts: 43

Re: Do Loop Dates macro

Hi RW9,

Thank you for your solution and I can use this for some queries that I'll make. But for my current query a raw extraction of single file will have 1 million+ observation so this will maximize my work resources when i try to append a months data. Anyway, this helps a lot.


-Albert
Super Contributor
Posts: 398

Re: Do Loop Dates macro

[ Edited ]

Can someone help me enhance the code above if for example even if I didn't trigger it for 1 week when I run the code the days that aren't cover will run without manually setting the date?

 

@Albert0

 

I would approach it a different way.  Rather than manually setting a date suffix, which may or may not match your filenames, or otherwise fiddling with what has and hasn't been processed, instead assume that whatever files are in your source directory are files that need to be processed.  Then, after successful processing, move them to an archive directory.

 

First, go here and download the dirlist and parmv macros:  https://github.com/scottbass/SAS/tree/master/Macro

 

Make sure they are in your sasautos path (or %include them into your program).

 

Then, run this code (modify as needed of course):

 

%dirlist(dir=/sas/logs)

data files / view=files;
   length line $1000;
   set dirlist;
   infile dummy filevar=fullname end=eof firstobs=2;
   do until (eof);
      input line;
      output;
   end;
run;

proc append data=files base=mydata;
run;

I don't have access to Unix, but AFAIK the %dirlist macro should work properly there.  Let me know if you encounter any issues.

 

After successful processing of your files, move the processed files from /sas/logs to /sas/logs/archive (or somewhere out of your incoming files location).

 

If moving the files is undesired, then sure, get the max date of the last processed file from your text file, and apply a where clause to the dirlist.  This would rely on datestamped logs to enforce files "greater than" some reference date.  Do you need an end date (today()-1) as an upper bound to the files you process?  If today's file is in the directory, is it some incomplete file that you don't want to touch until tomorrow?

 

For enhanced processing, check the value of &syscc before executing proc append and archiving of the files, and only execute that portion of the code if there are no errors.

 

I'm not sure this needs macro; even %dirlist is not required, it's just a convenience and has some builtin error checking and path parsing.

 

Hope this helps...

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 354 views
  • 3 likes
  • 5 in conversation