topic Re: min Date conversions to character date in SAS Programming

min Date conversions to character date

SASAna — Tue, 08 Jun 2021 23:10:16 GMT

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

Re: min Date conversions to character date

PaigeMiller — Tue, 08 Jun 2021 23:14:56 GMT

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

Re: min Date conversions to character date

ballardw — Tue, 08 Jun 2021 23:22:36 GMT

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.

Re: min Date conversions to character date

PaigeMiller — Tue, 08 Jun 2021 23:33:55 GMT

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.

Re: min Date conversions to character date

SASAna — Wed, 09 Jun 2021 19:06:59 GMT

Thank you. worked very well.