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.
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.
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.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.