01-26-2015 08:01 AM
Hi, I have the following data sets with the following variables and values:
WDT_TIME /*Some date time value*/
Dt_code w_date_time c_id u_id Start_date End_date
01 31DEC2014:00:00:00 xxxx yyyy 01/01/2015 06/01/2015
02 15JAN2015:00:00:00 xxxx yyyy 02/01/2015 07/01/2015
03 30NOV2014:00:00:00 xxxx yyyy 03/01/2015 08/01/2015
04 20JUN2014:00:00:00 xxxx yyyy 04/01/2015 09/01/2015
Logic: I need to get the max of WDT_TIME from HAVE1 and match the value with the values of w_date_time variable in HAVE2. If a match is NOT found then insert the max of WDT_TIME value from HAVE1 in w_date_time in HAVE2 and assign c_id="set" ,u_id=" ", start_date=sysdate, end_date=31dec9999,flag=n and if a match is found let it be as it is.
My want is an UPDATED_HAVE2:
Dt_code w_date_time c_id u_id Start_date End_date flag
01 31DEC2014:00:00:00 set /*missing*/ sysdate value 31dec9999 N /* IF max of wdt_time value from have1 not found in w_date_time in have2*/
01-26-2015 08:08 AM
Sorry, finding it quite difficult to understand what your requirements are. From what I can gather something like:
create table WANT as
case when B.WDT_TIME is null then ...
else A.C_ID end as C_ID,
from HAVE2 A
left join HAVE1 B
01-26-2015 08:26 AM
You can push the max of WDT_TIME into a macro variable , then compare it with the obs in HAVE2 . like something :
select max(WDT_TIME ) into : max from have1;
if w_date_time = &max then do;....................
01-26-2015 05:03 PM
I'm a little confused by your example.
The max of WDT_TIME is 16JAN2015:00:00:00, but your updated_have2 is using 31DEC2014:00:00:00
Do you just want the updated record in your dataset and not any other records?
Are the dates SAS Dates or character dates?