topic Re: Summing data by ID in SAS Programming

Summing data by ID

sks521 — Mon, 15 Jul 2019 16:16:09 GMT

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

Re: Summing data by ID

Reeza — Mon, 15 Jul 2019 16:28:12 GMT

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

Re: Summing data by ID

sks521 — Tue, 16 Jul 2019 08:27:39 GMT

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!

Re: Summing data by ID

Reeza — Tue, 16 Jul 2019 14:54:30 GMT

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.

Re: Summing data by ID

sks521 — Tue, 16 Jul 2019 15:42:57 GMT

Thanks Reza.