Adapt the new IDs & new record of existing IDs with datatype conversion for some variables

[Sathish_jammy]

Hi all,

The table1 has new IDs with character datatype in date,var2 but the table2 has numeric datatype in date,var2. I need to update the new IDs & new record of existing IDs from table1 to table2. I'll share the sample set for clear understandings.

data table1;
input ID var1 var2$ var3 date$23.;
cards;
123 12 13 45 1975-03-13 08:56:51.353
123 45 89 78 1995-05-23 00:00:00
345 32 65 98 1999-08-04 00:00:00
345 78 45 12 2000-11-13 02:46:31.363
678 12 13 45 1999-08-04 00:00:00
911 15 26 54 2000-11-13 01:46:41.358
;

data table2;
input ID var1 var2 var3 date mmddyy10.;
format date mmddyy10.;
cards;
123 12 13 45 03/13/1975
345 32 65 98 08/04/1999
911 15 26 54 11/12/2000
;

Expected Output in table2:

ID	var1	var2	var3	date
123	12	13	45	03/13/1975
123	45	89	78	05/23/1995
345	32	65	98	08/04/1999
345	78	45	12	11/13/2000
678	12	13	45	08/04/1999
911	15	26	54	11/12/2000

Please suggest a code to solve the problem.

Thanks in advance!

[Jagadishkatam · Accepted solution]

please try the below code

proc sort data=table1;
by id var1;
run;

proc sort data=table2;
by id var1;
run;

data want;
merge table1(in=a rename=(date=datec var2=var2c)) table2(in=b);
by id var1;
if var2=. then var2=input(var2c,best.);
if date=. then date=input(scan(datec,1,' '),yymmdd10.);
run;

[biopharma]

Is there a typo in the date value in Table 1 OR Table 2 for ID=911. They are 1 day apart. What purpose does Table 2 serve? I am assuming it has more variables that you are interested in looking at. Otherwise, the date value can be directly converted from what you have in Table 1. Try this and see if this is what you want. 

proc sql ;
   create table want as
      select t1.id, t1.var1
           , input(t1.var2,best.) as var2
           , t1.var3
           , coalesce(t2.date,input(scan(t1.date,1),yymdd10.))
             as date format=yymmdd10.
         from table1 as t1
            left join table2 as t2
           on t1.id = t2.id
          and t1.var1 = t2.var1
          and input(t1.var2,best.) = t2.var2
          and t1.var3 = t2.var3
       ;
quit ;