BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SAS_SB
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

Thank you. This worked!

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 137 views
  • 0 likes
  • 2 in conversation