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
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.
not in (select cats(id,put(min(year_month),date9.))) from dataset2_2 where new_ind =1)
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.