Summing data by ID

[sks521]

Hi folks,

I have a data set as follows;

ID    Eventdate            practice           status 

1      01/03/2016          SMP                Intervention

1      03/09/2016          SMP                Intervention

1      04/12/2016          SMP                Intervention

1      01/03/2017          SMP                Intervention

1      09/06/2017          SMP                Intervention

1      12/09/2017          SMP                Intervention

1      01/12/2017          SMP                Intervention

1      01/05/2018          SMP                Intervention

2      01/03/2016          BMP                Control

2      03/09/2016          BMP                Control

2      04/12/2016          BMP                Control

2      01/03/2017          BMP                Control

2      09/06/2017          BMP                Control

2      12/09/2017          BMP                Control

Where each EventDate represent a visit to health center.

I want to create a data set to perform difference-in-difference analysis using proc mixed for which I need total number of visits for each id by following three time periods;

if EventDate<'01SEP2017'd then month4='-1';*pre-intervention;
else if '01SEP2017'd <=EventDate<= '28FEB2018'd then month4='0';*intervention;
else if EventDate >= '01MAR2018'd then month4='1';*post-intervention;

basically creating wide data set as follows;

ID       Eventdate              practice          status           Total_visits_preintervention    Total_visits-Intervention  Total_Visits_postintervention

Can someone help how do I do that please?

Thanks

S

[Reeza · Accepted solution]

1. Create a format for your dates to map to intervention type
2. Run a PROC FREQ on the data set and the results will be as expected. Remember to apply the format within PROC FREQ and you should be fine, it will group the data automatically. You can have them the labels you want as well, Pre-Intervention, Intervention, they don't need to be numbers unless you're worried about sort order which is fair.

Here's a more complicated example than what you have but should give you the idea.

https://gist.github.com/statgeek/1c6f38ef368a4272cf458b017fc63d4b

This one, last example is what you need:
https://github.com/statgeek/SAS-Tutorials/blob/master/proc_format_example.sas

[sks521]

Thanks Reza,

It worked perfectly fine! can I ask you another related question?

Where do I add other variables like age, sex etc. in this syntax to keep them in the data set as I will be using them in the adjusted models?

Appreciate your help!

[Reeza]

Assuming they are things that are consistent across all rows per ID you could add them to a BY statement. If they are not consistent, ie age changes you'll need to create a summarized data set and merge it in with the main data set.

[sks521]

Thanks Reza.