BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Albert0
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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
error_prone
Barite | Level 11
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Albert0
Quartz | Level 8
Thank you Kurt. The code works fine.


-Albert
Albert0
Quartz | Level 8
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
ScottBass
Rhodochrosite | Level 12

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...

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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