I have the following if-else conditions within a SAS macro:
%if &restart_flg = Y %then %do;
%if %sysfunc(exist(&library.f2)) %then %do;
proc sql;
Connect to xxxxx as dbcon (user=xx pw=xx server=xx port=xxx database=xxxxx);
select * into : prcs_flag_cnt
from connection to dbcon (
select count(1)::smallint
from &library.f2
where flag = 1 and pflag <> 'N'
);
quit;
%put Process count flag: &prcs_flag_cnt;
%if &prcs_flag_cnt > 0 %then %do;
%let rflag = Y;
%end;
%else %do;
%let rflag = N;
%end;
%end;
%else %do;
%let rflag = N;
%end;
%end;
%else %do;
%let rflag = N;
%end;
I am basically checking if a particular execution is a fresh execution or a restarted one and populate the rflag accordingly (Y for restart, N for fresh start). Firstly I am checking if restart_flg is Y, if so, I check the existence of a SAS dataset (flags2), and if it exists, I check if any of the records exist with "flag = 1 and pflag <> 'N'" condition and then populate Y or N for rflag accordingly. If the restart_flg is N in first place, the rflag is set to N.
I find the way multiple if conditions written in the code to be less elegant. Is there any better way of writing this if-else condition or accomplishing this functionality?
Thanks!
You should disconnect from the database when your processing is done. Here, you create a connection to the database
each time the necessary conditions are verified which can get you eventually rejected by the server.
Using Astounding's advice, your program could be modified as :
%let rflag=N;
proc sql;
Connect to xxxxx as dbcon (user=xx pw=xx server=xx port=xxx database=xxxxx);
%if &restart_flg = Y and %sysfunc(exist(&library.f2)) %then %do;
SELECT * INTO : prcs_flag_cnt
FROM connection to dbcon (
SELECT count(1)::smallint
FROM &library.f2
WHERE flag = 1 AND pflag <> 'N'
);
%put Process count flag: &prcs_flag_cnt;
%if &prcs_flag_cnt > 0 %then %do;
%let rflag = Y;
%end;
%end;
disconnect from dbcon;
quit;
There is a risk of useless connection to the database though,if the condition is never verified.
You'll have to check this ...
It looks like you can get rid of most of %IF/THEN statements by adding this statement at the top of the program:
%let rflag=N;
There are just a handful of cases where you would want to later replace N with Y.
You should disconnect from the database when your processing is done. Here, you create a connection to the database
each time the necessary conditions are verified which can get you eventually rejected by the server.
Using Astounding's advice, your program could be modified as :
%let rflag=N;
proc sql;
Connect to xxxxx as dbcon (user=xx pw=xx server=xx port=xxx database=xxxxx);
%if &restart_flg = Y and %sysfunc(exist(&library.f2)) %then %do;
SELECT * INTO : prcs_flag_cnt
FROM connection to dbcon (
SELECT count(1)::smallint
FROM &library.f2
WHERE flag = 1 AND pflag <> 'N'
);
%put Process count flag: &prcs_flag_cnt;
%if &prcs_flag_cnt > 0 %then %do;
%let rflag = Y;
%end;
%end;
disconnect from dbcon;
quit;
There is a risk of useless connection to the database though,if the condition is never verified.
Macro != Elegant. Macro = fuggly obfuscated psuedocode.
Why would you have two different sets of code on runs? Proper startup/closedown routines which initialise and cleanout should remove any need to check if this has already run? Sounds to me like your just making coding for yourself. For instance there are three else if bloack all doing the same thing in your given code?
In whihc case that should be taken care of by access restrcitions, a user who can run from fresh would have different rights, and have access to their code, and vice versa. Should all be covered in your Functional Design Specifications, i.e. who access what at what level etc. Also, depends on the setup a bit, if you have modularised the process then its simply a matter of tracking models and pass/fail sucess, then picking up at the first fail, and running the chain from there.
The last three else's in the code you provide:
%else %do; %let rflag = N; %end; %end; %else %do; %let rflag = N; %end; %end; %else %do; %let rflag = N; %end;
They all do the same thing, i.e. setting rflag=N. Save yourself some typing and set rflag to N at top of program and only set it to Y on success - or have various levels:
rflag=Module1_Pass;
rflag=Module2_Pass;
...
So it is descriptive and you only need to alter it if something changes.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.