min Date conversions to character date

[SASAna]

Hi SAS Users,

I need a help.

I am updating a table with PROC SQL syntax and where condition is not working properly.

year_month value is coming as 01FEB2018 ( a date field) and concatenation needs character conversion , i converted it to put(year_month1,date9.) and looks like  ( ABC01FEB2018 which is id || put(year_month1,date9.)  )

on the right side, i have min(year_month) which is in SAS integer format, i need to convert this into  01FEB2018 format to get the merge correctly.

Or IS there any better way to merge this charcter-date field with min(date) ?

proc sql;
create table dataset2_2 as
select * from dataset2_1
;
update dataset2_1 a
set new_ind =99
where new_ind =1
and (id ||  put(year_month1,date9.)) not in ( select id || min(year_month) from dataset2_2 where new_ind =1)         /* not working */
;
Quit;

Thanks,

Ana

[PaigeMiller]

not in (select cats(id,put(min(year_month),date9.))) from dataset2_2 where new_ind =1)

[ballardw · Accepted solution]

You need to explicitly control all numeric to character conversions. Otherwise SAS will use a best format a likely have a bunch of leading blanks in part of the value especially if using the || operator to concatenate text.

Something like:  select  ID || Put(min(year_month),date9)

Personally instead of doing the concatenation of values in Dataset2_2 as a sub-query I would create a variable in the first bit:

proc sql;
   create table dataset2_2 as
   select * , cats(id,put(min(year_month),date9.)) as combid
   from dataset2_1
;

And then use the Combid (or whatever) in the update.

[SASAna]

Thank you. worked very well.

[PaigeMiller]

Not having SAS at my home computer, I am also skeptical if you can use the MIN function inside this type of SELECT clause, but I can't test it out right now. So as much as I hate to admit it, I think @ballardw is on the right track.