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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.