DATA Step, Macro, Functions and more

How to elegantly write multiple if-else conditions in SAS?

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

How to elegantly write multiple if-else conditions in SAS?

[ Edited ]

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!


Accepted Solutions
Solution
‎12-16-2016 10:12 AM
Regular Contributor
Posts: 233

Re: How to elegantly write multiple if-else conditions in SAS?

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 5,428

Re: How to elegantly write multiple if-else conditions in SAS?

In macro coding, not really.
I tvi k tar there is a ballot item for introducing a select - when construct in macros.
Not sure if it suits your situation but one way is convert to data step and use call symput to create your macro variables.
Data never sleeps
Super User
Posts: 5,503

Re: How to elegantly write multiple if-else conditions in SAS?

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.

Solution
‎12-16-2016 10:12 AM
Regular Contributor
Posts: 233

Re: How to elegantly write multiple if-else conditions in SAS?

[ Edited ]

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.

Super User
Super User
Posts: 7,952

Re: How to elegantly write multiple if-else conditions in SAS?

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?

Contributor
Posts: 20

Re: How to elegantly write multiple if-else conditions in SAS?

We have two different type of users in the system. One set of users alone need to have this restarability feature, and it should always be fresh run for other set of users. So two different sets of code exists. I do some further processing using rflag that will enable restart from point of failure as against fresh start from the beginning.

I am not sure I understand the last part of your question. Three if-else block don't do the same thing.
Super User
Super User
Posts: 7,952

Re: How to elegantly write multiple if-else conditions in SAS?

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 261 views
  • 0 likes
  • 5 in conversation