Pyrite | Level 9

## create a group of observations based on one observation

Hi,

I am trying to create a group of observations based on one observation.

by using table 1 as an example,

 obs ID A_date B_date C_date Action_A Action_B Action_C 1 1 2000 2003 2005 AAA positive B negative C 2 1 2015 2015 2016 B negative B negative C 3 2 2001 2002 2002 AA positive B positive C 4 2 2003 2003 2003 A positive B positive C

Introduction:

1.A_date is the date of Action_A action; B_date is the date of Action_B action, C_date is the date of Action_C action.

Requirement:

1.Value of Action_A collected from each row in Table 1 keep same in a group of new rows in TableB.

By using obs1 in Table1 as an example, the value in Action_A is ‘AAA’, I expect to create three new rows in TableB1 based on this observation, and the Action_A in these three rows are ‘AAA’.

2.There are three different scenarios for Action_B and Action_C.

The first two scenarios based on whether the date of their action equal A_date.

First scenario. If A_date^=B_date^=C_date^=. (please see obs1 in Table1), I expect to create TableB1

 TableB1 ID edate Action_A Action_BorC 1 2000 AAA stable B (A_date row) 1 2003 AAA positive B (B_date row) 1 2005 AAA negative C (C_date row)

A_date, B_date and C_date transpose as ‘edate’.

In A_date row, Action_BorC='stable B' as there is no action at the 'A_date' date.

Second scenario, if A_date=B_date^=C_date or A_date=C_date^=B_date (please see obs2 in Table1), I expect to create TableB2,

 TableB2 ID edate Action_A Action_BorC 1 2015 B negative B (A_date & B_date row) 1 2016 B negative C (C_date row)

In this example, as A_date=B_date=2015, then Action_BorC=’negative B’, which is the value in Action_B variable in obs2 in Table1.

The third scenario based on whether the Action_B and Action_C happen on the same date

Third scenario. If B_date=C_date (please see obs3 in Table1), I expect to create TableB3,

 TableB3 ID edate Action_A Action_BorC 2 2001 AA stable B (A_date row) 2 2002 AA wrong (B_date & C_date row)

The A_date row is processed as same as first scenario, as A_date^=B_date and A_date^=C_date.

I expect to get Action_BorC='wrong' in ‘B_date & C_date row’ as Action_B action and Action_C action happens on the same date is wrong.

It this scenario, if A_date= B_date=C_date=2003 (please see obs4 in Table1) then I expect to have

 TableB4 ID edate Action_A Action_BorC 2 2003 A wrong (A_date & B_date & C_date row)

```data table1;
infile cards dsd  dlm=",";
input
ID \$
A_date :4.
B_date :4.
C_date :4.
Action_A :\$50.
Action_B :\$50.
CAction_C :\$50.

;
cards;
1,2000,2003,2005,AAA,positive B,negative C
1,2015,2015,2016,B,negative B,negative C
2,2001,2002,2002,AA,positive B,positive C
2,2003,2003,2003,A,positive B,positive C
;;;;
run;

```
Rhodochrosite | Level 12

## Re: create a group of observations based on one observation

Hello,

Something like this ?

``````data B1 B2 B3 B4;
set table1;
length Action_BorC \$50.;

keep ID edate Action_A Action_BorC;
if A_date=B_date=C_date then do;
edate=A_date;
Action_BorC="wrong";
output B4;
end;
else if B_date=C_date then do;
edate=A_date;
Action_BorC="stable B";
output B3;
edate=B_date;
Action_BorC="wrong";
output B3;
end;
else if A_date=B_date or A_date=C_date then do;
edate=B_date;
Action_BorC=Action_B;
output B2;
edate=C_date;
Action_BorC=Action_C;
output B2;
end;
else do;
edate=A_date;
Action_BorC="stable B";
output B1;
edate=B_date;
Action_BorC=Action_B;
output B1;
edate=C_date;
Action_BorC=Action_C;
output B1;
end;
run;
``````

Discussion stats