topic Re: How to create a variable for use in a job in SAS Programming

How to create a variable for use in a job

Cugel — Wed, 13 Jan 2021 15:23:33 GMT

I want to create a variable that I can then use in any where statement. Something like this

DATA CREATE_JOB_SPECIFIC_VARS;
%let year_filter_1 = 2019;
%let year_filter_2 = extract(year from sysdate)-5;
RUN;

The intention is to create the variable in a "sql execute" or "user written" statement.
When I create the variable as mentioned in the example, the variable seems to have no content over time.

Re: How to create a variable for use in a job

Kurt_Bremser — Wed, 13 Jan 2021 15:58:59 GMT

You do not need that data step around the macro statements; macro code is resolved before any other code is handed off to the data step compiler or any procedures.

This creates and shows your macro variables:

%let year_filter_1 = 2019;
%let year_filter_2 = %eval(%sysfunc(year(%sysfunc(today())))-5);
%put &=year_filter_1;
%put &=year_filter_2;

Log:

 73         %let year_filter_1 = 2019;
 74         %let year_filter_2 = %eval(%sysfunc(year(%sysfunc(today())))-5);
 75         %put &=year_filter_1;
 YEAR_FILTER_1=2019
 76         %put &=year_filter_2;
 YEAR_FILTER_2=2016

Re: How to create a variable for use in a job

Cugel — Wed, 13 Jan 2021 18:10:13 GMT

Hi Kurt, Thanks for your help.
I am a SAS novice and it is not entirely clear to me where I can best include the code lines in the job. In the "sql execute" or in the "user written" statement.
Or somewhere else in the job ?. I am not yet very familiar with the structure of SAS macros.

Re: How to create a variable for use in a job

Tom — Wed, 13 Jan 2021 18:37:50 GMT

What tool are you using to build that work flow? Is it Enterprise Guide? SAS/Studio? Something else?

Re: How to create a variable for use in a job

Cugel — Wed, 13 Jan 2021 18:43:13 GMT

Hi Tom

Thanks for your help, we are using SAS Data Integration Studio 4.903.

Re: How to create a variable for use in a job

Cugel — Sun, 17 Jan 2021 13:11:50 GMT

I have included Kurt's solution in "SQL execute" transformation. 
In the job, this transformation is performed first. This solution works. 
However, if the job crashes, the variable is no longer available and the first step must be performed again.