data test; set test1; where term in ("FIRST A" "FIRST B"); if term="FIRST A" then t1sdt=input(date1,yymmdd10.); if term="FIRST B" then t2sdt=input(date1,yymmdd10.); format t1sdt t2sdt date9.; run;
in the above code the output for same id comes in 2 different rows since the conditions are different. Can i make one id like below
id t1sdt t2sdt
101 10MAR2011 19MAR2011
102 12MAR2019 20MAR2011
Currently it is coming like below
id t1sdt t2sdt
101 10MAR2011
101 19MAR2011
102 12MAR2019
102 20MAR2011
Possibly. But the approach will depend on your actual data.
For instance, does every single id value have exactly one record with term="FIRST A" and exactly one record with term="FIRST B"?
If the answer is no, and especially if there are more than one of either or both of the FIRST A and FIRST B values, then you will have to show the desired result.
You really should provide some example input data that shows what you start with.
This does what you show in the case of EXACTLY 2 records matching the Where condition for each ID.
data have; input id $ term $ date1 :$10.; datalines; 101 FIRSTA 20200101 101 FIRSTB 20200102 101 FIRSTC 20200103 102 FIRSTB 20200111 102 FIRSTA 20200112 ; data want; set have; where term in ("FIRSTA" "FIRSTB"); by notsorted id; retain t1sdt t2sdt; if term="FIRSTA" then t1sdt=input(date1,yymmdd10.); if term="FIRSTB" then t2sdt=input(date1,yymmdd10.); format t1sdt t2sdt date9.; if last.id then output; drop term date1; run;
It will likely do what you want if there is only a single record for each id unless they are not adjacent to each other in the data.
If you have more records for the ID that match the WHERE condition then you need 1) to provide actual examples and 2) what the exact output for the example should look like. Which may get fun if you have 7 or 8 matches.
Since you don't show us (a portion of) the input data, how can we write code (or help you write code) so that the desired results are obtained? We could guess what the input data looks like, but that is often a waste of time ...
In any event, this may not be the most efficient way to get the result you want, but you could take the output you show and then
proc summary data=test nway;
class id;
var t1sdt t2sdt;
output out=want sum=;
run;
Here's a version that somewhat resembles your DATA step:
data test;
do until (last.id);
set test1;
by id;
where term in ("FIRST A" "FIRST B");
if term="FIRST A" then t1sdt=input(date1,yymmdd10.);
else if term="FIRST B" then t2sdt=input(date1,yymmdd10.);
end;
format t1sdt t2sdt date9.;
run;
Possibly. But the approach will depend on your actual data.
For instance, does every single id value have exactly one record with term="FIRST A" and exactly one record with term="FIRST B"?
If the answer is no, and especially if there are more than one of either or both of the FIRST A and FIRST B values, then you will have to show the desired result.
You really should provide some example input data that shows what you start with.
This does what you show in the case of EXACTLY 2 records matching the Where condition for each ID.
data have; input id $ term $ date1 :$10.; datalines; 101 FIRSTA 20200101 101 FIRSTB 20200102 101 FIRSTC 20200103 102 FIRSTB 20200111 102 FIRSTA 20200112 ; data want; set have; where term in ("FIRSTA" "FIRSTB"); by notsorted id; retain t1sdt t2sdt; if term="FIRSTA" then t1sdt=input(date1,yymmdd10.); if term="FIRSTB" then t2sdt=input(date1,yymmdd10.); format t1sdt t2sdt date9.; if last.id then output; drop term date1; run;
It will likely do what you want if there is only a single record for each id unless they are not adjacent to each other in the data.
If you have more records for the ID that match the WHERE condition then you need 1) to provide actual examples and 2) what the exact output for the example should look like. Which may get fun if you have 7 or 8 matches.
Hi @noda6003
You can use a proc transpose for example to achieve this:
data test1;
input id term $ 5-11 date1 $ 13-20;
datalines;
101 FIRST A 20110310
101 FIRST B 20110319
102 FIRST A 20190312
102 FIRST B 20110320
;
run;
proc print;
data test2;
set test1;
format t1sdt t2sdt date9.;
where term in ("FIRST A" "FIRST B");
date = input(date1,yymmdd10.);
if term="FIRST A" then tsdt = "t1sdt";
else if term="FIRST B" then tsdt = "t2sdt";
run;
proc transpose data=test out=test3 (drop=_name_);
var date;
by id;
id tsdt;
run;
data want;
set test3;
format t1sdt t2sdt date9.;
run;
Hi @noda6003
You can use a proc transpose for example to achieve this:
data test1;
input id term $ 5-11 date1 $ 13-20;
datalines;
101 FIRST A 20110310
101 FIRST B 20110319
102 FIRST A 20190312
102 FIRST B 20110320
;
run;
proc print;
data test2;
set test1;
format t1sdt t2sdt date9.;
where term in ("FIRST A" "FIRST B");
date = input(date1,yymmdd10.);
if term="FIRST A" then tsdt = "t1sdt";
else if term="FIRST B" then tsdt = "t2sdt";
run;
proc transpose data=test out=test3 (drop=_name_);
var date;
by id;
id tsdt;
run;
data want;
set test3;
format t1sdt t2sdt date9.;
run;
Hi @noda6003 I am going with my assumption with some proc sql solution. @Astounding 's solution is by far the fastest and elegant if your dataset is sorted by ID.
FWIW
proc sql;
create table want as
select id, max((term="FIRST A")*input(date1,yymmdd10.)) as t1sdt,
max((term="FIRST B")*input(date1,yymmdd10.)) as t2sdt
from your_dataset
where term in ("FIRST A" "FIRST B")
group by id
order by id;
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.