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

[kajal_30]

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;

[SASKiwi]

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;

[kajal_30]

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.

[Quentin]

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;

[Tom]

---

@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;

[Kurt_Bremser]

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.

[Quentin]

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<< ;