topic Re: multiple if condition in SAS Programming

multiple if condition

noda6003 — Fri, 03 Jan 2020 15:33:29 GMT

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

Re: multiple if condition

PaigeMiller — Fri, 03 Jan 2020 15:45:10 GMT

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;

Re: multiple if condition

Astounding — Fri, 03 Jan 2020 15:46:33 GMT

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;

Re: multiple if condition

ballardw — Fri, 03 Jan 2020 15:51:23 GMT

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.

Re: multiple if condition

ed_sas_member — Fri, 03 Jan 2020 15:52:22 GMT

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;

Re: multiple if condition

ed_sas_member — Fri, 03 Jan 2020 15:53:08 GMT

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;

Re: multiple if condition

novinosrin — Fri, 03 Jan 2020 16:11:02 GMT

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;