Effective Data manipulation help?

Reply
Contributor
Posts: 70

Effective Data manipulation help?

Hi,  I have the following data sets with the following variables and values:

HAVE1:

WDT_TIME   /*Some date time value*/

28DEC2014:00:00:00

16JAN2015:00:00:00

29NOV2014:00:00:00

11JUN2014:00:00:00

HAVE2:

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*/

Esteemed Advisor
Esteemed Advisor
Posts: 7,211

Re: Effective Data manipulation help?

Sorry, finding it quite difficult to understand what your requirements are.  From what I can gather something like:

proc sql;

     create table WANT as

     select     A.DT_CODE,

                    A.W_DATE_TIME,

                    case     when B.WDT_TIME is null then ...

                                 else A.C_ID end as C_ID,

                    ...

     from       HAVE2 A

     left join   HAVE1 B

     on          A.W_DATE_TIME=B.WDT_TIME;

quit;

Grand Advisor
Posts: 9,576

Re: Effective Data manipulation help?

You can push the max of WDT_TIME    into a macro variable  , then compare it with the obs in HAVE2  .  like something :

proc sql;

select max(WDT_TIME ) into : max from have1;

quit;

data want;

set have2;

if w_date_time = &max then do;....................

Occasional Contributor
Posts: 15

Re: Effective Data manipulation help?

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?

Ask a Question
Discussion stats
  • 3 replies
  • 223 views
  • 0 likes
  • 4 in conversation