Hi there!
I am trying to call a PROC SQL or another based on an input variable.
My input variable is like YYYYMM, and if MM is equal to 01 then I do a new table else I need to merge with the table from the month before.
Is there anyway to do this?
I tried but I keep failing it doesn't create a table.
Thanks in advance.
The code I tried was something like:
%let month=%substr(&delta,5,2); %macro january; proc sql; create table WORK.RH_SOLV_RECEITA_EMITIDA_&delta as select * from WORK.SOLV_RECEITA_EMITIDA_MES; quit; %mend janeiro; %macro outros; proc sql; create table WORK.RH_SOLV_RECEITA_EMITIDA_&delta as select * FROM WORK.SOLV_RECEITA_EMITIDA_OLD_MES outer union corr select * FROM WORK.SOLV_RECEITA_EMITIDA_MES; quit; %mend outros; data _null_; if &month = '01' then call execute('%janeiro'); if &delta <> '01' then call execute('%outros'); run;
So I copied the code @Kurt_Bremser gave me and pasted in the project.
Result, didn't work.
Copied it into a new project, worked perfectly.
Closed and ran the project again and now the code works.
Turns out, with a few improvements (like "<>" replaced by "ne"), was working.
It was a bug in the project which got solved by a restart. 😑 🙄
Thanks for your time @Kurt_Bremser and @Tom.
If macro variable delta contains this:
%let delta=202001;
Then this
if &delta <> '01'
resolves to
if 202001 <> '01'
which will always be false.
The data step will convert your number to a string on its own, but that won't help either.
delta is in the text format, never the less I changed the data to
%let month=%substr(&delta,5,2); %macro january; proc sql; create table WORK.RH_SOLV_RECEITA_EMITIDA_&delta as select * from WORK.SOLV_RECEITA_EMITIDA_MES; quit; %mend january; %macro outros; proc sql; create table WORK.RH_SOLV_RECEITA_EMITIDA_&delta as select * FROM WORK.SOLV_RECEITA_EMITIDA_OLD_MES outer union corr select * FROM WORK.SOLV_RECEITA_EMITIDA_MES; quit; %mend outros; data _null_; if &month = 1 then call execute('%january'); if &month <> 1 then call execute('%outros'); run;
and still it doesn't create a table.
Maxim 2: Read the Log. <> is not interpreted by the data step compiler in the way you think.
Start simple, and then expand on this code:
%macro january;
%put january executing;
%mend january;
%macro outros;
%put outros executing;
%mend outros;
%let delta=202001;
%let month=%substr(&delta,5,2);
data _null_;
if &month = 1 then call execute('%january');
if &month ne 1 then call execute('%outros'); /* use the proper operator for not equals */
run;
%let delta=202002;
%let month=%substr(&delta,5,2);
data _null_;
if &month = 1 then call execute('%january');
if &month ne 1 then call execute('%outros');
run;
This line has an obviously TRUE test condition.
if &month <> 1 then call execute('%outros');
It really does not matter what value MONTH has since the MAXimum value of 1 or &MONTH is never going to be zero or missing the test will always be TRUE.
Perhaps you are trying to test if &MONTH is NOT EQUAL to 1? If so then use an appropriate operator for that.
So I copied the code @Kurt_Bremser gave me and pasted in the project.
Result, didn't work.
Copied it into a new project, worked perfectly.
Closed and ran the project again and now the code works.
Turns out, with a few improvements (like "<>" replaced by "ne"), was working.
It was a bug in the project which got solved by a restart. 😑 🙄
Thanks for your time @Kurt_Bremser and @Tom.
You may have had an "Autoexec" process flow in your project that caused this.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.