Get a count of service received by the dates and group by the ID

[SAS_SB]

Hello,

I have a dataset (example) with following variables: ID, bhs_time (to determine if the services received by the individuals were during pre-testing or post-testing period), service date, hospital admission date, and jail admission date. 

data admissions;
infile datalines missover;
input id bhs_time $ SERVICE_DATE :mmddyy10. ADMISSION_DATE_HOSP :mmddyy10. ADMISSION_DATE_JAIL :mmddyy10.;
format SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL mmddyy10.;
cards;
1	post_period	9/17/2017		.		.
1	post_period	9/14/2017		.		.
1	post_period	9/19/2017		.		.
1	post_period	9/14/2017		.		.
2	post_period	9/9/2019		.		.
2	post_period	3/7/2020		.		.
2	post_period	3/12/2020		.		.
2	post_period	.			3/7/2020	.
2	pre_period	.			8/18/2020	.
2	post_period	8/13/2019		.		.
2	post_period	4/2/2020		.		.
3	post_period	8/1/2019		.		.
3	post_period	9/3/2021		.		.
4	pre_period	7/2/2018		.		.
4	pre_period	7/2/2018		.		.
5	post_period		.			.	3/18/2022
6	post_period		.			.	3/14/2020
7	post_period	1/24/2021		.		.
7	post_period	1/24/2021		.		.
8	post_period		.			.	4/2/2018
8	pre_period		.			.	10/12/2017
9	post_period	7/19/2020		.		.
9	post_period	7/18/2020		.		.
9	post_period	5/8/2020		.		.
9	post_period	6/20/2020		.		.
;
run;

I need to create a table that has a count for pre-period and post-period of each service dates, hospital admission dates, and jail admission dates grouped by the IDs. I want the following table:

ID	Count_pre_period_service	Count_post_period_service	Count_pre_period_admission	Count_post_period_admission	Count_pre_period_jail	Count_post_period_jail
1	0	4	.	.	.	.
2	0	5	1	1	.	.
3	0	2	.	.	.	.
4	2	0	.	.	.	.
5	.	.	.	.	0	1
6	.	.	.	.	0	1
7	0	2	.	.	.	.
8	.	.	.	.	1	1
9	0	4	.	.	.	.

I would appreciate any help with the code.

Thank you!

[ballardw]

Without creating any additional variables, which would be needed to have pre_this  and Post_that like your column headings look:

proc tabulate data=admissions;
   class id bhs_time;
   var SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL;
   table id,
         bhs_time=' '*(SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL)*n=' '
   ;
run;

You could change the appearance of the variable names in the columns by either using a LABEL for the variable or adding an override with the name like: service_date="Service Date". The =' ' after a variable suppress appearance of a row or column label with a variable name or statistic in Proc Tabulate.

Note: Your data step only reads 8 character of the Bhs_time variable.

Or If you want the pre/post next to each other in the table change the order of the variables:

proc tabulate data=admissions;
   class id bhs_time;
   var SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL;
   table id,
        (SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL)* bhs_time=' '*n=' '
   ;
run;

Proc tabulate allows more than one TABLE statement so could create both in one call to the proc.

If you want total, such as across all of the ID values:

proc tabulate data=admissions;
   class id bhs_time;
   var SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL;
   table id ALL='Total',
        (SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL)* bhs_time=' '*n=' '
   ;
run;

And if you want the total of pre and post:

proc tabulate data=admissions;
   class id bhs_time;
   var SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL;
   table id All='Total',
        (SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL)* (bhs_time=' ' All=Total)*n=' '
   ;
run;

Note the parentheses to group variables into similar roles of the syntax.

[SAS_SB]

Thanks @ballardw !

I tried the codes. What I want is to have a missing value (.) for the observations if the dates are missing instead of zero (0).

One request, how can I create a dataset instead of just having a table in the "Results Viewer" because I need to add other variables to the dataset later on for further analysis?

Thank you!

[ballardw · Accepted solution]

Most of the SAS counting procedures will return 0. That basically means a pass through a data step to change to missing when the value is 0.

Here is an approach to create a data set similar to your picture

proc summary data=admissions nway ;
   class id bhs_time;
   var SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL;
   output out=counts (drop= _type_ _freq_) n=;
run;

data useful;
   set counts;
   array v (*)   SERVICE_DATE ADMISSION_DATE_HOSP ADMISSION_DATE_JAIL;
   do i=1 to dim(v);
      NewVarName= catx('_',vname(v[i]),bhs_time);
      Value=v[i];
      if value=0 then value=.;
      output;
   end;
   keep id newvarname value;
run;

proc transpose data=useful out=want(drop=_name_);
   by id;
   id NewVarName;
   var value;
run;

The bit in the data step, besides setting the 0 to your desired ., creates a variable to hold a desired name and reshapes the data. Then TRANSPOSE will use that set to use the new name variable as the actual name and make the set with the pre and post across. Modify the code creating NewVarName as desired.

[SAS_SB]

Thank you. This worked!