multiple if condition

[noda6003]

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

[PaigeMiller]

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;

[Astounding]

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;

[ballardw · Accepted solution]

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.

[ed_sas_member]

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;

[ed_sas_member]

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;

[novinosrin]

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;