topic Re: Append weekly data to create a to date Financial year dataset in SAS Programming

Append weekly data to create a to date Financial year dataset

hammerman — Thu, 18 Feb 2016 05:32:07 GMT

Program is run every week for the previous Friday ( for example: 15/02/2016) a report should be run with end date as (12/02/2016).

So, a week's start date is Saturday and End date is a Friday.

For running this a Autoexec is setup which gets the WKstart, EndDATE by providing something like this.

%let trig_file=150619;

%INCLUDE "/sasdata/abc/environment/dev/autoexec.sas";

This gets resolved and values such as are generated, which I use in my code.

RUNDATE = 20160212

ENDDATE = 20160212

STARTDATE = 20160206

TDDate= 1160212

FinYear_start = 20150701

This is my example code :

%let trig_file=XX_150619;

%INCLUDE "/sasdata/abc/environment/dev/autoexec.sas";

Proc Sql;

connect to Teradata (&tdcon);

Create table work.report&RUNDATE as Select * from connection to teradata

(

Select * from emp

where

extract_date <= &TdDATE

and DOE between &StartDate and EndDate;

) ;

disconnect from teradata;

quit;

Proc append Base= Save.FYTD_report data= report&RUNDATE;

run;

I want to embed this code into to a macro which can be run will from start of financial year till last friday by providing

%concat_report(XX_150703);

%concat_report(XX_150710);

How can I do this ?

I tried doing this but doesn't work

%macro concat_report(rdate=);

%let trig_file=&rdate;

%INCLUDE "/sasdata/abc/environment/dev/autoexec.sas";

PROC SQL;

blah blah

PROC APPEND blah ;

%mend concat_report;

%concat_report( XX_150619);

Re: Append weekly data to create a to date Financial year dataset

Kurt_Bremser — Thu, 18 Feb 2016 07:17:26 GMT

What happened (log?)

For further diagnosis, we will need the exact code of the whole macro, as macro programming can (and will) be tricky.

In your first example, trig_file is purely numeric, but then you use the XX_ prefix. Are you sure your auotexec.sas can deal with that correctly?

Re: Append weekly data to create a to date Financial year dataset

RW9 — Thu, 18 Feb 2016 09:40:48 GMT

Sorry, its not clear to me why you need that code to create various things and append, and then have it in a macro? The SQL code which gets the data from the database can directly insert the data into the required dataset. But then the question arises, why are you subsetting the data inserted, but don't appear to do anything with the subset, and then require a macro to keep doing it? Just select all the data and insert it?

proc sql;
  connect to teradata (&TDCON.);
  insert into SAVE.FYTD_REPORT
  select * from connection to teradata
  (
    select  *  
    from    EMP
    where   EXTRACT_DATE <= &TDDATE.
      and   DOE between &STARTDATE. and ENDDATE;
  );
  disconnect from teradata;
quit;

Note, notice the use of {i} to post code as this keeps formatting. Also note that easy to read code is far better to other people reading your code, note the consitent indentations, consistent capitilisation, finishing macvro variables with "." etc.