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;

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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