conditional merging of two data tables, is it possible in SAS ? how?

[imanojkumar1]

I have a table 1 which has the following data as show in the below code:

 
    data have(index=(date));
    infile cards truncover expandtabs;
    input TPMC    PWC    PWSC $    Site    ET    $ Date : date9.    Time $ DIAM    $ PXMC    $ SF;
    format date date9.;
    cards;
    7101    7101    US000521    1    Works1    08Nov2016    11:58    890,3         1
    7102    7102    US000361    1    Works2    02Nov2016    13:01    878,1         1
    7102    7102    UC000348    2    Works3    07Nov2016    18:22    877,3         1
    7106    7106    UC00424    1    Works1    05Oct2016    9:43    890,4         1
    7106    7106    UC00437    3    Works2    07Nov2016    18:23    877,1         1
    7106    7106    UC309    4    Works3    07Nov2016    18:26    877,8         1
    7107    7107    UC05327    1    Works1    06Oct2016    8:41    837         1
    7107    7107    UC200    2    Works2    13Oct2016    12:53    890,55         1
    7108    7108    UC000361    3    Works1    02Nov2016    13:01    878,1         1
    7108    7108    UC00432    1    Works2    07Nov2016    18:25    877,8         1
    7108    7108    UC106    2    Works3    03Oct2016    9:37    890,3         1
    ;
    run;
        
    data want;
     set have;
     array x{1} _temporary_;
    array y{1} $ 40 _temporary_;
     x{1}=date;y{1}=TPMC;
     call missing(of _all_);
     TPMC=y{1};Time=0;
     do i=5 to 1 by -1;
       date=x{1}-i;output;
     end;
     set have point=_n_;output;
     call missing(of _all_);
     TPMC=y{1};Time=0;
     do i=1 to 5;
       date=x{1}+i;output;
     end;
     drop i;
     run;

and I have another table like the one below:

    data have0001;
      input WPmeas $ Date :date8. Time :Time. T_Id $ ASN $ WC $ CWC T_Side M_No $ Yval $ XVal $;
      FORMAT DATE date8. Time HHMM.;
    datalines;
    1 19JAN16 12:19 20949 1    7604 US2301 R 1 -82140 2468
    2 21JAN16 11:11 20949 1    7604 US2302 R 2 -81940 2466
    3 01FEB16 16:13 20949 1    7604 US2303 R 3 -81739 2463
    4 29MAR16 13:12 20949 1    7604 US2303 R 4 -81539 2459
    5 13MAY16 14:11 20949 1 7604 US2304 R 5 -81339 2456
    6 05OCT16 15:15 20949 1    7106 US2301 R 6 -82140 2468
    7 07NOV16 16:18 20949 1 7106 US2302 R 7 -81940 2466
    8 01FEB16 17:15 20949 1 7604 US2303 R 8 -81739 2463
    9 29MAR16 18:17 20949 1 7604 US2303 R 9 -81539 2459
    10 13MAY16 19:12 20949 1 7604 US2304 R 10 -81339 2456
    ;
    run;

I am just trying to find out the ways to do the following:

1. select t1.PWSC = t2.CWC and t1.TPMC = t2.WC

2. select Dates from t1 (t1.Date) = t2.Date

3. select Time from t1 (t1.time) and only first value of time from t2.Time (i.e. if we sort Time in ascending order, the first will be 00:00, provided it is there otherwise whichever is first is picked up)

then

4. Merge the two datasets.

In other words what I am needing is to retrieve data from **t2** and merge with **t1** only when the dates are matching, the WC/TPMC and CWC/PWSC are matching. and Also, the time in t2 is the first observation in asc. order.

Please help in learning.

Thanks.

[LinusH]

Untested, but first select the first observation from t2 (first sort, the data step if first. or a second PROC SORT with NODUPKEY).

The you should be able to do a"simple" SQL join with criteria you listed.

To be sure that we understand the requirement, it's recommended that you provide a "want" sample data set.

[ballardw]

You reference "merging" T1 and T2 but your example data sets are Have, Want and Have001. Better would be to either use HAVE and HAVE001 or other example data set instead of T1 and T2 in the requirement OR to name the datasets T1 and T2 in the example. Context may be sufficient to get the point across but it is poor form to make people examine variable names to determine which set is which.