BookmarkSubscribeRSS Feed
hammerman
Obsidian | Level 7

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);

 

2 REPLIES 2
Kurt_Bremser
Super User

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 2 replies
  • 966 views
  • 0 likes
  • 3 in conversation