BookmarkSubscribeRSS Feed
Alexxxxxxx
Pyrite | Level 9

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;

1 REPLY 1
gamotte
Rhodochrosite | Level 12

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;

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 651 views
  • 0 likes
  • 2 in conversation