topic Re: Conditional PROC SQL in SAS Data Management

Conditional PROC SQL

RicHen — Tue, 07 Apr 2020 15:34:00 GMT

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;

Re: Conditional PROC SQL

Kurt_Bremser — Tue, 07 Apr 2020 15:40:37 GMT

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.

Re: Conditional PROC SQL

RicHen — Tue, 07 Apr 2020 16:12:36 GMT

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.

Re: Conditional PROC SQL

Kurt_Bremser — Tue, 07 Apr 2020 16:20:16 GMT

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;

Re: Conditional PROC SQL

RicHen — Tue, 07 Apr 2020 16:31:00 GMT

Will do.

Back on the topic when I have more news.

Thanks for your support @Kurt_Bremser.

Re: Conditional PROC SQL

Tom — Tue, 07 Apr 2020 18:37:25 GMT

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.

Re: Conditional PROC SQL

RicHen — Wed, 08 Apr 2020 16:07:39 GMT

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.

Re: Conditional PROC SQL

RicHen — Wed, 08 Apr 2020 16:38:47 GMT

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

Re: Conditional PROC SQL

Kurt_Bremser — Thu, 09 Apr 2020 06:48:47 GMT

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

Re: Conditional PROC SQL

RicHen — Thu, 09 Apr 2020 16:37:48 GMT

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