DATA Step, Macro, Functions and more

SAS Automation-Running a report for JUST a few previous days...

Reply
Occasional Contributor
Posts: 19

SAS Automation-Running a report for JUST a few previous days...

[ Edited ]

Im a creature of my own sucess.. I have a current automated report that uses a file from the previous day that runs fantastic @4:30AM , so mgmt has a report waiting for them @ 6:00AM...... However, mgmt now would also like to see a seperate report that not only includes the previous day but the previous 3 days as well

 

The current method is that I get a file at 4am that includes the previous days data that is dropped into a folder that is date based ..  For example, the current file ie "core_fed_ack_gen_data02_01.tsv" is in folder "02.01.17"... So the following SAS script knows to find it that folder and to export it to another folder that also uses the "02.01.17" scheme....  

 

The issue is that they also now want to see about 3 days of the previous data as well so they have it for comparison...

 

This is my current code:

 

data _null_;
xx = date();
yy = dhms(intnx('day',xx,-1),0,0,0);
zz = dhms(xx,3,0,0);
length
  this_day this_month this_year $2
  this_year4 $4
  myfilename $100
;
this_day = put(day(xx),z2.);
this_month = put(month(xx),z2.);
this_year4 = put(year(xx),z4.);
this_year = substr(this_year4,3);

myfilename = 'C:\Users\username\Documents\MeF\automate\PROD\' !! this_year4 !!'\GZ\' !! put(xx,mmddyyp8.) !! '\core_fed_ack_gen_data' !! this_month !! '_' !! this_day !! '.tsv';
myoutputnm = 'C:\Users\username\Documents\MeF\automate\PROD\' !! this_year4 !!'\rpt\' !! put(xx,mmddyyp8.) !! '\core_fed_ack_gen_data' !! this_month !! '_' !! this_day !! '.png';
myoutputcsv = 'C:\Users\username\Documents\MeF\automate\PROD\' !! this_year4 !!'\rpt\' !! put(xx,mmddyyp8.) !! '\core_fed_ack_gen_data' !! this_month !! '_' !! this_day !! '.csv';

call symput('filepath',strip(myfilename));
call symput('filesout',strip(myoutputnm));
call symput('filesoutcsv',strip(myoutputcsv));
call symput('begdt',"'"!!put(yy,datetime18.1)!!"'dt");
call symput('enddt',"'"!!put(zz,datetime18.1)!!"'dt");
/*
%let begdt = '23Feb16 00:00:00.0'dt ;
%let enddt = '24Feb16 03:00:00.0'dt ;
*/
%let sync_int = 600;
%let ginterval='01:00:00.0't ; 
run ;
%put filepath=&filepath;
%put filesout=&filesout;
%put begdt=&begdt;
%put enddt=&enddt;

filename filein (
   "&filepath"
 );

....SAS code.. to crunch the data file . 

proc export
   data=ALL
   outfile="&filesoutcsv"
   dbms=csv
   replace
;

It's not to difficult to flag some additional days as value/parameter...  basically this to go back 3 more days

 

 

yy = dhms(intnx('day',xx,-4),0,0,0); 

ie the -4 to go back a few days....The problem I am running into in how to specify folders ... right now it picks up the folder in

myfilename = 'C:\Users\username\Documents\MeF\automate\PROD\' !! this_year4 !!'\GZ\' !! put(xx,mmddyyp8.) !! '\core_fed_ack_gen_data' !! this_month !! '_' !! this_day !! '.tsv';

This code goes to C:\Users\username\Documents\MeF\automate\PROD\2017\GZ\02.01.17 to grab the .tsv file...  However, I now need it to to back to C:\Users\username\Documents\MeF\automate\PROD\2017\GZ\01.29.17 thru 02.01.17 

 

 

I figured I could try and leverage this command

this_day = put(day(xx),z2.);

and create something like

 

 

this_day = put(day(xx),z2.); 
this_dayb2 = put(day(xx, -1),z2.);
this_dayb3 = put(day(xx, -2),z2.);
this_dayb4 = put(day(xx, -3),z2.);

but I don't know how to add it to the the "myfilename".. Im close, but missing something..

 

 

Thanks in advance

 

Super User
Posts: 11,343

Re: SAS Automation-Running a report for JUST a few previous days...

Posted in reply to hondahawkrider

When I had a loosely similar project I had established SAS libraries to hold the daily datasets (or append them into a continuous set depending on the project). Then there was no concern about "folders". It sounds like your process was going to re-read the raw data multiple times. Is that the case?

 

With a continuous data set if the data has a date-of-record variable as a SAS date then subsetting to the desired data is:

 

data want;

    set longdata;

    where DateOfRecord ge (today() -4);

run;

Super User
Posts: 19,815

Re: SAS Automation-Running a report for JUST a few previous days...

Posted in reply to hondahawkrider

Why not build yourself a little database. Every day when you read the file, append it to your master table and then query from that table for your 3 day report. 

Valued Guide
Posts: 505

Re: SAS Automation-Running a report for JUST a few previous days...

This might help?

Hits #77 Using  a ring of four generations of a SAS dataset

inspired by
https://goo.gl/FQJGmj
https://communities.sas.com/t5/Base-SAS-Programming/SAS-Automation-Running-a-report-for-JUST-a-few-previous-days/m-p/329245

* documentation;
http://analytics.ncsu.edu/sesug/2006/SC18_06.PDF

With generation datasets you do not need to change your
code with daily dataset refreshes.

The ring of datasets rolls off the latest dataset and adds the newest, so
you can print yesterdays and todays data using, with relative addressing.

proc print data=genum(gennum=-1);run;quit;  * yesterdays data load;
proc print data=genum;run;quit;             * todays dataload

You have easy access to yesterdays data


HAVE ( Will show you how to create these)
==========================================

            Gen   Member
#  Name     Num   Type  One variable doc)                   Also Dataset name

1  GENUM          DATA  Base DOC='This is Fri Feb 3, 2017'  GENUM
2  GENUM      -3  DATA  DOC=This is Tue Jan 31, 2017        GENUM#001
3  GENUM      -2  DATA  DOC=This is Wed Feb 1, 2017         GENUM#002
4  GENUM      -1  DATA  DOC=This is Thu Feb 2, 2017         GENUM#003

proc print data=genum(gennum=-3);run;quit;
proc print data=genum(gennum=-2);run;quit;
proc print data=genum(gennum=-1);run;quit;
proc print data=genum(gennum;run;quit;

Tue Jan 31, 2017
Wed Feb 1, 2017
Thu Feb 2, 2017
Fri Feb 3, 2017

WANT One day later. Roll off "Tue Jan 31, 2017"(oldest) and add  "Sat Feb 3,2017";
==================================================================================

* this updates the ring;
data genum;
  retain doc 'This is Sun Feb 5, 2017';
run;quit;

Run exactly the same proc print code but drop off the oldest;

proc print data=genum(gennum=-3);run;quit;
proc print data=genum(gennum=-2);run;quit;
proc print data=genum(gennum=-1);run;quit;
proc print data=genum(gennum;run;quit;

Wed Feb 1, 2017
Thu Feb 2, 2017
Fri Feb 3, 2017
Sat Feb 4, 2017


SOLUTION
========

proc datasets lib=work kill;
run;quit;

* prime the ring this is a base not interested in this one;
data genum(genmax=4);
  retain doc 'This is Tue Jan 31, 2017';
run;quit;

data genum;
  retain doc 'This is Wed Feb 1, 2017';
run;quit;

data genum;
  retain doc 'This is Thu Feb 2, 2017';
run;quit;

data genum;
  retain doc 'This is Fri Feb 3, 2017';
run;quit;

proc contents data=work._all_;
run;quit;

/*
            Gen    Member
#  Name     Num    Type

1  GENUM           DATA
2  GENUM     -3    DATA
3  GENUM     -2    DATA
4  GENUM     -2    DATA
*/

Now lets roll the oldest one off and todays (same code)
=======================================================

data genum;
  retain doc 'This is Sat Feb 4, 2017';
run;quit;

* lets see what happend;

proc contents data=work._all_;
run;quit;

* new ring;

proc report data=genum(gennum=-3);
run;quit;

proc report data=genum(gennum=-2);
run;quit;

proc report data=genum(gennum=-1);
run;quit;

proc report data=genum;
run;quit;

This is Wed Feb 1, 2017
This is Thu Feb 2, 2017
This is Fri Feb 3, 2017
This is Sat Feb 4, 2017


Do it again  (same code)
===========

data genum;
  retain doc 'This is Sun Feb 5, 2017';
run;quit;

proc contents data=work._all_;
run;quit;

* new ring;

proc report data=genum(gennum=-3);
run;quit;

proc report data=genum(gennum=-2);
run;quit;

proc report data=genum(gennum=-1);
run;quit;

proc report data=genum;
run;quit;

This is Thu Feb 2, 2017
This is Fri Feb 3, 2017
This is Sat Feb 4, 2017
This is Sat Feb 4, 2017

Ask a Question
Discussion stats
  • 3 replies
  • 157 views
  • 0 likes
  • 4 in conversation