Macro variable null check based on count(*)

[rajdeep]

Hi Team,

I am trying to check count(*) of a dataset variable whether it's null or not null. If it's null then I want to assign zero or else the original count(*) value to the variable.

data work.test;
format alert_id best12.
create_date date9.;
stop;
run;
proc sql noprint;
select count(ALERT_ID) into:ALERT_COUNT
from test
where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
quit;
%if %length(%superq(&ALERT_COUNT))=0 %then %let ALERT_COUNT=0;
%else %let ALERT_COUNT=&ALERT_COUNT;

I tried this, but it's somehow not working out. So, basically I just trying like, if input dataset is null then ALERT_COUNT should go as zero or else the count(ALERT_ID). Can someone please help me out how to achieve this.

Thanks

[PaigeMiller]

%let alert_count=0;
proc sql noprint;
select count(ALERT_ID) into:ALERT_COUNT
from test
where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
quit;

If you really need to test if a macro variable is null, see http://support.sas.com/resources/papers/proceedings09/022-2009.pdf

[rajdeep]

Hi Paigemiller,

Thanks a lot for the workaround, I tried to follow your suggestion with little bit of modification, but still I am not getting the result as expected.

data work.test;
/*input alert_id create_date datetime20.;*/
format alert_id best12.
create_date datetime20.;
stop;
run;
%let alert_count1=0;
proc sql noprint;
select count(ALERT_ID) into:ALERT_COUNT2
from test
where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
quit;
%global ALERT_COUNT2;

%macro Dep_Scen_Mail;
%if %sysevalf(%superq(ALERT_COUNT2)=,boolean)  %then %let ALERT_COUNT=&alert_count1;
%else %let ALERT_COUNT=&ALERT_COUNT2;
%mend;
%Dep_Scen_Mail;
%put &Job_Status &ALERT_COUNT &ALERT_COUNT2 &ALERT_COUNT1;

I think the if else condition is not working properly because of some gap. In the log the null check condition is appearing as true, as the input dataset is null and ALERT_COUNT2 resolve to null, but as per the if condition it should assign the ALERT_COUNT1 value to ALERT_COUNT variable, but it's not happening.

Log for your reference:

23         GOPTIONS ACCESSIBLE;
24         data work.test;
25         /*input alert_id create_date datetime20.;*/
26         format alert_id best12.
27         create_date datetime20.;
28         stop;
29         run;

NOTE: Variable alert_id is uninitialized.
NOTE: Variable create_date is uninitialized.
NOTE: The data set WORK.TEST has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

30         
31         %let alert_count1=0;
32         proc sql noprint;
33         select count(ALERT_ID) into:ALERT_COUNT2
34         from test
35         where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
NOTE: No rows were selected.
36         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

37         %global ALERT_COUNT2;
38         
39         %macro Dep_Scen_Mail;
40         %if %sysevalf(%superq(ALERT_COUNT2)=,boolean)  %then %do; %let ALERT_COUNT=&alert_count1;%end;
41         %else %do;%let ALERT_COUNT=&ALERT_COUNT2;%end;
2                                                          The SAS System                                 18:00 Tuesday, May 4, 2021

42         %mend;
43         %Dep_Scen_Mail;
MLOGIC(DEP_SCEN_MAIL):  Beginning execution.
MLOGIC(DEP_SCEN_MAIL):  %IF condition %sysevalf(%superq(ALERT_COUNT2)=,boolean) is TRUE
MLOGIC(DEP_SCEN_MAIL):  %LET (variable name is ALERT_COUNT)
SYMBOLGEN:  Macro variable ALERT_COUNT1 resolves to 0
MLOGIC(DEP_SCEN_MAIL):  Ending execution.
44         %put &Job_Status &ALERT_COUNT &ALERT_COUNT2 &ALERT_COUNT1;
SYMBOLGEN:  Macro variable JOB_STATUS resolves to Job Successful                                                                  
WARNING: Apparent symbolic reference ALERT_COUNT not resolved.
SYMBOLGEN:  Macro variable ALERT_COUNT2 resolves to 
SYMBOLGEN:  Macro variable ALERT_COUNT1 resolves to 0
Job Successful                                                                   &ALERT_COUNT  0
45         

Please guide. Also, please let me know if I am doing something wrong.

[Patrick]

In your code you need to fix the highlighted bits below:

But if you just need a macro variable &alert_count with a zero in case there aren't any rows in the source table then all the code you need is what @PaigeMiller already posted. This replaces ALL of your code.

[Tom]

The unneeded (unwanted) GROUP BY is what is causing the trouble.  If you remove that then the SQL query will update (or create) the macro variable even when no observations meet the WHERE condition.

Why is it there?  Did you want to use COUNT(DISTINCT ALERT_ID) instead?

[rajdeep]

Hi Tom,

I need the alert_id counts based on the individual create_date, as the alerts will be loaded everyday, so how many alerts are loaded on the current day basis, for that purpose I need the group by clause.

Thanks

[Tom]

---

@rajdeep wrote:

Hi Tom,

I need the alert_id counts based on the individual create_date, as the alerts will be loaded everyday, so how many alerts are loaded on the current day basis, for that purpose I need the group by clause.

Thanks

---

That does not make any sense in multiple ways. 

• The code was using the WHERE clause to limit the counting to a single date so there is no need to group.
• The code was only taking one count value into one macro variable so if you did want to generate individual counts per individual dates only one of them would have been saved.

Because of the GROUP BY with a query that reads 0 observations the select statement does not write anything into the macro variable since there are zero groups.  But without the GROUP BY the query counts zero non-missing values of ALERT_ID and so writes "0" into the macro variable.

Another useful coding pattern is to set the default value into the macro variable before running the query. Then if the query does not return any result the macro variable exists and has a well defined value.

proc sql noprint;
%let test1=Query Failed;
%let test2=Query Failed;
select count(name) into :test1 trimmed from sashelp.class
  where sex='0'
  group by age
;
select count(name) into :test2 trimmed from sashelp.class
  where sex='0'
;
%put &=test1 &=test2;
quit;

Results

2691  %put &=test1 &=test2;
TEST1=Query Failed TEST2=0

[Kurt_Bremser]

proc sql noprint;
select count(ALERT_ID) into:ALERT_COUNT2
from test
where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
quit;

If CREATE_DATE has more than one timepoint for a given date, the GROUP BY will create several values, but only the first will be selected into the macro variable.

See this for reference:

proc sql;
select count(name) into :count
from sashelp.class
group by sex;
quit;

%put &=count.;

[PaigeMiller]

---

@rajdeep wrote:

Hi Paigemiller,

Thanks a lot for the workaround, I tried to follow your suggestion with little bit of modification, but still I am not getting the result as expected.

data work.test;
/*input alert_id create_date datetime20.;*/
format alert_id best12.
create_date datetime20.;
stop;
run;
%let alert_count1=0;
proc sql noprint;
select count(ALERT_ID) into:ALERT_COUNT2
from test
where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
quit;
%global ALERT_COUNT2;

%macro Dep_Scen_Mail;
%if %sysevalf(%superq(ALERT_COUNT2)=,boolean)  %then %let ALERT_COUNT=&alert_count1;
%else %let ALERT_COUNT=&ALERT_COUNT2;
%mend;
%Dep_Scen_Mail;
%put &Job_Status &ALERT_COUNT &ALERT_COUNT2 &ALERT_COUNT1;

I think the if else condition is not working properly because of some gap. In the log the null check condition is appearing as true, as the input dataset is null and ALERT_COUNT2 resolve to null, but as per the if condition it should assign the ALERT_COUNT1 value to ALERT_COUNT variable, but it's not happening.

Log for your reference:

23         GOPTIONS ACCESSIBLE;
24         data work.test;
25         /*input alert_id create_date datetime20.;*/
26         format alert_id best12.
27         create_date datetime20.;
28         stop;
29         run;

NOTE: Variable alert_id is uninitialized.
NOTE: Variable create_date is uninitialized.
NOTE: The data set WORK.TEST has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

30         
31         %let alert_count1=0;
32         proc sql noprint;
33         select count(ALERT_ID) into:ALERT_COUNT2
34         from test
35         where datepart(CREATE_DATE)=DATE() group by CREATE_DATE;
NOTE: No rows were selected.
36         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

37         %global ALERT_COUNT2;
38         
39         %macro Dep_Scen_Mail;
40         %if %sysevalf(%superq(ALERT_COUNT2)=,boolean)  %then %do; %let ALERT_COUNT=&alert_count1;%end;
41         %else %do;%let ALERT_COUNT=&ALERT_COUNT2;%end;
2                                                          The SAS System                                 18:00 Tuesday, May 4, 2021

42         %mend;
43         %Dep_Scen_Mail;
MLOGIC(DEP_SCEN_MAIL):  Beginning execution.
MLOGIC(DEP_SCEN_MAIL):  %IF condition %sysevalf(%superq(ALERT_COUNT2)=,boolean) is TRUE
MLOGIC(DEP_SCEN_MAIL):  %LET (variable name is ALERT_COUNT)
SYMBOLGEN:  Macro variable ALERT_COUNT1 resolves to 0
MLOGIC(DEP_SCEN_MAIL):  Ending execution.
44         %put &Job_Status &ALERT_COUNT &ALERT_COUNT2 &ALERT_COUNT1;
SYMBOLGEN:  Macro variable JOB_STATUS resolves to Job Successful                                                                  
WARNING: Apparent symbolic reference ALERT_COUNT not resolved.
SYMBOLGEN:  Macro variable ALERT_COUNT2 resolves to 
SYMBOLGEN:  Macro variable ALERT_COUNT1 resolves to 0
Job Successful                                                                   &ALERT_COUNT  0
45         

Please guide. Also, please let me know if I am doing something wrong.

---

My code didn't have macro variables ALERT_COUNT1 and ALERT_COUNT2. It only showed one macro variable ALERT_COUNT. Not sure why you changed it, but that causes the problems you are seeing.