What is the best statement SET or Merge to get the desired PE output. I need to join the tables by PID and retain only the records from table P. Also need to have a field called source in PE to identify if the record is in both or P.
Thank you
Table P | Table E | |||
PID | SID1 | PID | SID2 | |
1 | 123 | 1 | 124 | |
2 | 24 | 2 | 24 | |
3 | 43 | 4 | 33 | |
5 | 26 | 5 | 23 | |
6 | 15 | |||
Table PE | ||||
PID | SID1 | SID2 | ||
1 | 123 | 124 | ||
2 | 24 | 24 | ||
3 | 43 | |||
5 | 26 | 23 |
@Stalk wrote:
What is the best statement SET or Merge to get the desired PE output. I need to join the tables by PID and retain only the records from table P. Also need to have a field called source in PE to identify if the record is in both or P.
Thank you
Table P Table E PID SID1 PID SID2 1 123 1 124 2 24 2 24 3 43 4 33 5 26 5 23 6 15 Table PE PID SID1 SID2 1 123 124 2 24 24 3 43 5 26 23
When you want to MATCH records from two (or more) data sets based on the value of a common variable then you want a Merge with a By;
data p; input Pid sid1; datalines; 1 1 2 2 ; data e; input enrolid sid2; datalines; 1 11 3 33 ; data merged; merge p (in=in1) e (in=in2) ; /* the first IF keeps records that match the id field in the first set */ if in1; If in1 and in2 then source='Both'; else if in1 then source='P'; run;
The data set option IN= creates a temporary variable valued 1 or 0 if the data set contributes to the current record. SAS uses 1 for true and 0 for false so can be used in If as above.
what is the best - merge or set?
NO, SQL Left join !
data p;
input Pid sid1;
datalines;
1 123
2 24
3 43
5 26
;
data e;
input pid sid2;
datalines;
1 124
2 24
4 33
5 23
6 15
;
run;
proc sql;
create table PE as
select p.pid, sid1, sid2
from p left join e
on p.pid=e.pid;
quit;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.