...

SQL, get historical status of each ID

 

In the sas system, table tab stores the patients’ medical history. Visit_code=Surgery means that the patient is in surgery; Visit_code=Office means the patient is in consultation room. Each patient has more than one Visit_code; one may only have Surgery or Office. Below is the table:

Id

Visit_Date

Visit_code

A30

5/15/2004

Surgery

A30

2/5/2005

Office

B01

12/7/2002

Office

B01

11/21/2002

Surgery

C01

12/1/2001

Office

C01

11/1/2001

Office

C01

5/15/2001

Surgery

C01

4/15/2001

Surgery

C02

12/1/2001

Surgery

C03

12/1/2001

Office

Task: Get the historical medical status for each patient. If they once got consultation, record Office_Visit as 1 (Office_Visit=1), otherwise record it as 0; if they got consultation six months after their surgery, record SX_past_6mo as 1 (SX_past_6mo=1), otherwise record it as 0:

Id

Office_Visit

SX_past_6mo

A30

1

0

B01

1

1

C01

1

1

C02

0

0

C03

1

0

Write the following SPL code:

A

1

=sas1.query("select * from tb order by Id,Visit_Date")

2

=A1.group(Id)
.new(Id,(S=0,~.max( if( Visit_code=="Office", if( S==0 && Visit_code[-1]=="Surgery" && interval@m(Visit_Date[-1],Visit_Date)<=6,S=1,1),0))):Office_Visit, S: SX_past_6mo)

A1: Retrieve data from sas through JDBC and sort data by Visit_code and Visit_Date.

A2: Group data by Id and create a new two-dimensional table based on the groups. ~ is the current group, Visit_code[-1] represents Visit_code field of the previous record; interval@m gets the number months between two dates.

Source: https://stackoverflow.com/questions/78443712/indicate-whether-a-date-is-6-months-before-another-date-for-the-same-id-column


 
 •  67 View  •