topic Re: How to do the coalesce of macro variable and today's date in SAS Programming

How to do the coalesce of macro variable and today's date

kajal_30 — Wed, 03 Aug 2022 04:19:56 GMT

I have a program as below. Where I am getting value from the existing table. Incase existing table has no value how can I do the coalesce ? Here in case source data has no values. I tried code below but it is not working. I have another non null value in a macro variable &yearmonth .

proc sql;
	select coalesce (put(max(yearmonth),6.) , &yearmonth)) into :  yrmonth from source_data;
quit;

proc sql;
	delete * from target_data
		where yearmonth= &yrmonth.;
quit;

Re: How to do the coalesce of macro variable and today's date

SASKiwi — Wed, 03 Aug 2022 04:37:30 GMT

You can't use COALESCE here as if there are no rows in SOURCE_DATA the SQL statement won't execute. Assign YRMONTH first, then overwrite it with your SQL if SOURCE_DATA is populated:

%let yrmonth = %substr(%sysfunc(today(), yymmddn8.), 1, 6);;
%put &yrmonth;

proc sql;
  select put(yearmonth,6.) into  :yrmonth from source_data;
quit;

Re: How to do the coalesce of macro variable and today's date

Kurt_Bremser — Wed, 03 Aug 2022 06:13:52 GMT

You need to set a "default" before the SQL:

%let yrmonth = &yearmonth;
proc sql noprint;
select put(max(yearmonth),6.) into :  yrmonth from source_data;
quit;

Your SQL DELETE will not work because of a syntax error. Omit the asterisk.

And it's not clear which type the variables in your datasets are, so you need to decide where to use quotes.

Re: How to do the coalesce of macro variable and today's date

Quentin — Wed, 03 Aug 2022 12:41:44 GMT

Agree with others that assigning a default value is the way to do it. But just as a thought exercise, you can use COALESCE via %sysfunc, e.g.:

%symdel yrmonth /nowarn ;

data source_data ;
  yearmonth=202201 ;
  delete ;
run ;

options missing=' ' ;
proc sql;
	select put(max(yearmonth),6.) into :  yrmonth from source_data ;
quit;
options missing=. ;

%put >>&yrmonth<< ;

%let yrmonth=%sysfunc(CoalesceC(&yrmonth,%substr(%sysfunc(today(), yymmddn8.), 1, 6))) ;

%put >>&yrmonth<< ;

Re: How to do the coalesce of macro variable and today's date

kajal_30 — Wed, 03 Aug 2022 13:01:57 GMT

This will give me a non- null value for sure

%let yrmonth = %substr(%sysfunc(today(), yymmddn8.), 1, 6);;
%put &yrmonth;

but I am wondering if it will be replaced with the null value in the next step if there are no records in the source data?

Please correct me if I am wrong.

Re: How to do the coalesce of macro variable and today's date

Quentin — Wed, 03 Aug 2022 13:06:13 GMT

If you're not sure whether it will be replaced or not, you can test it out, e.g.:

%symdel yrmonth /nowarn ;

data source_data ;
  yearmonth=202201 ;
  delete ;
run ;

%let yrmonth = %substr(%sysfunc(today(), yymmddn8.), 1, 6);;

proc sql;
  select put(yearmonth,6.) into  :yrmonth from source_data;
quit;

%put &yrmonth;

Re: How to do the coalesce of macro variable and today's date

Tom — Wed, 03 Aug 2022 13:20:32 GMT

@kajal_30 wrote:

This will give me a non- null value for sure
%let yrmonth = %substr(%sysfunc(today(), yymmddn8.), 1, 6);;
%put &yrmonth;
but I am wondering if it will be replaced with the null value in the next step if there are no records in the source data?

Please correct me if I am wrong.

Depends on whether the SQL query returns any results or not. For your simple query that depends on whether the dataset has any observations (even observations with missing values of the variable). So you migth want to add a WHERE to your query to prevent it from overwriting the default value with a missing value.

%let yrmonth=&yearmonth;
proc sql noprint;
select max(yearmonth) format=z6.
  into :yrmonth 
  from source_data
  where not missing(yearmonth)
;
quit;