BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASdn
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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

6 REPLIES 6
LinusH
Tourmaline | Level 20
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
Astounding
PROC Star

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.

gamotte
Rhodochrosite | Level 12

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

SASdn
Obsidian | Level 7
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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