Conditional PROC SQL

[RicHen]

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;

[Kurt_Bremser]

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.

[RicHen]

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.

[Kurt_Bremser]

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;

[RicHen]

Will do.

Back on the topic when I have more news.

Thanks for your support @Kurt_Bremser.

[Tom]

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. 

[RicHen · Accepted solution]

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.

[RicHen]

*Turns out, with a few improvements (like "<>" replaced by "ne"), "the original code" was working.

[Kurt_Bremser]

You may have had an "Autoexec" process flow in your project that caused this.

[RicHen]

Possibly... I had a lot of tryouts before I reached this code.