create a group of observations based on one observation

Alexxxxxxx — Sat, 26 Sep 2020 08:38:45 GMT

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)

Could you please give me some suggestions about this?

Thanks in advance.

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;

Re: create a group of observations based on one observation

gamotte — Tue, 29 Sep 2020 14:49:41 GMT

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;

topic Re: create a group of observations based on one observation in SAS Programming

create a group of observations based on one observation

Re: create a group of observations based on one observation