PROC TABULATE or PROC REPORT

[cj9000]

Hi, I am trying to create a custom report that looks like the below: I have code that gets me to the and throough the means proc but after that I am lost as to how to get the data presented. I have tried proc report and proc tabulate with no success. I did transpose the data after the means proc. Here is a sampling of the data:

The question is: How many patients ever had a discharge_disposition of “not recorded”.
What was there visit_type? (It’s possible for a single patient to have several “not recorded” discharge disposition.)
2.What was the total number of “not recorded” and what was it for each visit_type.
3. Columns are still based on vist type
4. Should use proc freq
5. Use the code for assignment #3 the not recorded program

Thanks you in advance!

Original data:

Obs	PTID	VISITID	VISIT_TYPE	VISIT_START_DATE	VISIT_END_DATE	DISCHARGE_DISPOSITION	vstdt	vendt	newdt
1	PT000000022	673482272	Emergency patient	10/04/2008	10/04/2008	01 Discharged to home or self care	04OCT2008	04OCT2008	.
2	PT000000022	673482273	Emergency patient	11/10/2008	11/10/2008	01 Discharged to home or self care	10NOV2008	04OCT2008	.
3	PT000000022	673482274	Emergency patient	07/30/2011	07/30/2011	01 Discharged to home or self care	30JUL2011	10NOV2008	.
4	PT000000022	673482271	Inpatient	06/21/2010	06/22/2010	01 Discharged to home or self care	21JUN2010	22JUN2010	.
5	PT000000099	610952548	Emergency patient	08/24/2010	08/24/2010	01 Discharged to home or self care	24AUG2010	24AUG2010	.
6	PT000000099	610952549	Emergency patient	08/14/2012	08/14/2012	01 Discharged to home or self care	14AUG2012	24AUG2010	.
7	PT000000099	610952547	Inpatient	03/21/2010	03/23/2010	01 Discharged to home or self care	21MAR2010	23MAR2010	.
8	PT000000132	610888961	Emergency patient	11/27/2009	11/27/2009	01 Discharged to home or self care	27NOV2009	27NOV2009	.
9	PT000000159	655709236	Emergency patient	01/22/2007	01/22/2007	01 Discharged to home or self care	22JAN2007	22JAN2007	.
10	PT000000159	655709237	Emergency patient	03/30/2007	03/31/2007	01 Discharged to home or self care	30MAR2007	22JAN2007	.
11	PT000000159	655709238	Emergency patient	04/29/2007	04/30/2007	01 Discharged to home or self care	29APR2007	31MAR2007	.
12	PT000000159	655709239	Emergency patient	05/06/2007	05/06/2007	01 Discharged to home or self care	06MAY2007	30APR2007	.
13	PT000000159	655709240	Emergency patient	08/31/2007	08/31/2007	01 Discharged to home or self care	31AUG2007	06MAY2007	.
14	PT000000159	655709241	Emergency patient	09/17/2007	09/17/2007	01 Discharged to home or self care	17SEP2007	31AUG2007	.
15	PT000000159	655709242	Emergency patient	04/30/2008	04/30/2008	01 Discharged to home or self care	30APR2008	17SEP2007	.
16	PT000000159	655709243	Emergency patient	08/06/2008	08/06/2008	01 Discharged to home or self care	06AUG2008	30APR2008	.
17	PT000000159	655709244	Emergency patient	09/17/2008	09/17/2008	01 Discharged to home or self care	17SEP2008	06AUG2008	.
18	PT000000159	655709245	Emergency patient	11/30/2008	11/30/2008	01 Discharged to home or self care	30NOV2008	17SEP2008	.
19	PT000000159	655709246	Emergency patient	12/12/2008	12/12/2008	01 Discharged to home or self care	12DEC2008	30NOV2008	.
20	PT000000159	655709247	Emergency patient	01/03/2009	01/03/2009	01 Discharged to home or self care	03JAN2009	12DEC2008	.
21	PT000000159	655709248	Emergency patient	01/09/2009	01/09/2009	01 Discharged to home or self care	09JAN2009	03JAN2009	.
22	PT000000159	655709249	Emergency patient	02/28/2009	02/28/2009	01 Discharged to home or self care	28FEB2009	09JAN2009	.
23	PT000000159	655709250	Emergency patient	05/09/2009	05/10/2009	01 Discharged to home or self care	09MAY2009	28FEB2009	.
24	PT000000159	655709251	Emergency patient	05/19/2009	05/19/2009	01 Discharged to home or self care	19MAY2009	10MAY2009	.
25	PT000000159	655709252	Emergency patient	09/09/2009	09/10/2009	01 Discharged to home or self care	09SEP2009	19MAY2009	.
26	PT000000159	655709253	Emergency patient	11/20/2009	11/20/2009	01 Discharged to home or self care	20NOV2009	10SEP2009	.
27	PT000000159	655709254	Emergency patient	02/16/2010	02/16/2010	01 Discharged to home or self care	16FEB2010	20NOV2009	.
28	PT000000159	655709255	Emergency patient	04/11/2010	04/11/2010	01 Discharged to home or self care	11APR2010	16FEB2010	.
29	PT000000159	655709256	Emergency patient	06/12/2010	06/12/2010	07 Left against medical advice	12JUN2010	11APR2010	.
30	PT000000159	655709257	Emergency patient	06/29/2010	06/29/2010	01 Discharged to home or self care	29JUN2010	12JUN2010	.
31	PT000000159	655709258	Emergency patient	07/13/2010	07/13/2010	01 Discharged to home or self care	13JUL2010	29JUN2010	.
32	PT000000159	655709259	Emergency patient	08/07/2010	08/07/2010	01 Discharged to home or self care	07AUG2010	13JUL2010	.
33	PT000000159	655709260	Emergency patient	08/23/2010	08/23/2010	01 Discharged to home or self care	23AUG2010	07AUG2010	.
34	PT000000159	655709261	Emergency patient	11/22/2010	11/23/2010	01 Discharged to home or self care	22NOV2010	23AUG2010	.
35	PT000000159	655709262	Emergency patient	01/25/2011	01/26/2011	01 Discharged to home or self care	25JAN2011	23NOV2010	.
36	PT000000159	655709263	Emergency patient	05/01/2011	05/01/2011	01 Discharged to home or self care	01MAY2011	26JAN2011	.
37	PT000000159	655709264	Emergency patient	08/19/2011	08/19/2011	01 Discharged to home or self care	19AUG2011	01MAY2011	.
38	PT000000159	655709265	Emergency patient	05/07/2012	05/07/2012	01 Discharged to home or self care	07MAY2012	19AUG2011	.
39	PT000000159	655709266	Emergency patient	11/05/2012	11/06/2012	01 Discharged to home or self care	05NOV2012	07MAY2012	.
40	PT000000159	655709223	Inpatient	06/03/2007	06/07/2007	01 Discharged to home or self care	03JUN2007	07JUN2007	.
41	PT000000159	655709224	Inpatient	01/20/2008	01/21/2008	01 Discharged to home or self care	20JAN2008	07JUN2007	.
42	PT000000159	655709225	Inpatient	10/24/2010	10/26/2010	01 Discharged to home or self care	24OCT2010	21JAN2008	.
43	PT000000159	655709226	Observation patient	12/14/2009	12/14/2009	01 Discharged to home or self care	14DEC2009	14DEC2009	.
44	PT000000225	611184911	Inpatient	06/29/2012	07/01/2012	06 Discharged/transferred to home	29JUN2012	01JUL2012	.
45	PT000000225	611184912	Inpatient	10/05/2012	10/09/2012	01 Discharged to home or self care	05OCT2012	01JUL2012	.
46	PT000000334	682072164	Emergency patient	06/08/2009	06/08/2009	Not recorded	08JUN2009	08JUN2009	.
47	PT000000334	682072163	Inpatient	06/09/2009	06/10/2009	06 Discharged/transferred to home	09JUN2009	10JUN2009	.
48	PT000000337	697620729	Emergency patient	12/03/2010	12/03/2010	01 Discharged to home or self care	03DEC2010	03DEC2010	.
49	PT000000337	697620730	Emergency patient	12/10/2010	12/10/2010	01 Discharged to home or self care	10DEC2010	03DEC2010	.
50	PT000000378	682406411	Emergency patient	01/24/2008	01/24/2008	01 Discharged to home or self care	24JAN2008	24JAN2008	.

The code:

libname shrrel "c:/assignment";
proc sql ;
    create    table vis as
    select    *
        ,    max(visit_type) as maxvis
        ,    max(upcase(discharge_disposition)="NOT RECORDED") as max_nr
        ,    sum(upcase(discharge_disposition)="NOT RECORDED") as sum_nr
    from    shrrel.visit
    group by ptid
    order by ptid,visitid
    ;
quit ;

proc print data=shrrel.visit (obs=100);
run;
*** get total column ;
data vistot ;
    set vis (obs=1000) ;
    output; 
    
    maxvis='Total' ;
    output ; 
run ;

******* (1) get number of patients and (2) number of not recorder per visit type ;
proc sort data=vistot out=npt nodupkey ;
    by ptid maxvis ;
run ;

proc freq data=npt ;
    table maxvis / out=npt1 ;
   table maxvis*max_nr / out=npt2 outpct ;
run ;/*
proc print data= stat_nr;
run;*/

**** (3) summary stats of not recorded ;
proc means data=npt nway ;
    class maxvis ;
    var sum_nr ;
    output out=stat_nr n=n mean=mean std=std median=median min=min max=max ;
run ;

/*****
proc print data=npt2;
run; ***/

proc transpose data=stat_nr out = transposed name=Not_Rec;
	ID maxvis;
	var n mean std median min max;
run;
/*
proc print data= transposed;
run; */

/* just some proc tabulate attempts of mine. 
proc tabulate data=npt;
   class maxvis  ;
  
   table maxvis  ;
run;

proc tabulate data=transposed;
   class Not_Rec Emergency_patient Inpatient Observation_patient Total   ;
  
   table Not_Rec, Emergency_patient Inpatient Observation_patient Total   ;
run;

proc report data = transposed;
run;

The Report:

	Emergency (n=##)	Inpatient (n=##)	Observation (n=##)	Total (n=###)
Not Recorded	N (%)	N (%)	N (%)	N (%)
Number of Not Recorded
N	##	##	##	###
Mean (SD)	##.# (##.##)	##.# (##.##)	##.# (##.##)	##.# (##.##)
Median	##	##	##	###
Min, Max	##, ##	##, ##	##, ##	##, ##

[Cynthia_sas]

Hi:
Take a look at Example 1 and Example 8. This is not a PROC TABULATE report. It is either a job for PROC REPORT, DATA step with TABLE templates or the RWI (report writing interface). I would probably stick with PROC REPORT, as shown in this paper:
http://www2.sas.com/proceedings/forum2008/173-2008.pdf
cynthia

[cj9000]

Thank you Cynthia. Example 8 is definitely what I am interested in. By chance is there any available code that produced this example?

[Cynthia_sas · Accepted solution]

Hi:
You could also do Example 8 with PROC REPORT, I just did not do that example with PROC REPORT. The programs that go with the paper are available from: https://support.sas.com/rnd/papers/#SGF2008 (look for the title of the paper and then the Dowload link -- you won't see my name on this page, just the paper title).

[cj9000]

Thank you Cynthia. Is there any particular reason you did the example in Proc tabulate?

I was able to download it and will study and work with it this evening. I will probably have questions. smile.

Thank you.

Charlesj

[ballardw]

This link https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show you how to generate a SAS datastep of your dataset. Then you can include that or attach as a TXT file so we don't get things like your table pasted running over the margins of the display  area.

Also the data step would allow us to test code. That pasted example would require us to read the data with the possibility of us getting variable types incorrect.

[cj9000]

Thanks Ballardw for the link, the process wasn't too hard to follow.

The requested data step;

data SHRREL.TestVisit;
  infile datalines dsd truncover;
  input PTID:$11. VISITID:BEST12. VISIT_TYPE:$19. VISIT_START_DATE:MMDDYY10. VISIT_END_DATE:MMDDYY10. DISCHARGE_DISPOSITION:$34. vstdt:DATE9. vendt:DATE9. newdt:32.;
datalines4;
PT000000022,673482272,Emergency patient,10/04/2008,10/04/2008,Not recorded,04OCT2008,04OCT2008,
PT000000022,673482273,Emergency patient,11/10/2008,11/10/2008,01 Discharged to home or self care,10NOV2008,04OCT2008,
PT000000022,673482274,Emergency patient,07/30/2011,07/30/2011,Not recorded,30JUL2011,10NOV2008,
PT000000022,673482271,Inpatient,06/21/2010,06/22/2010,01 Discharged to home or self care,21JUN2010,22JUN2010,
PT000000099,610952548,Emergency patient,08/24/2010,08/24/2010,01 Discharged to home or self care,24AUG2010,24AUG2010,
PT000000099,610952549,Emergency patient,08/14/2012,08/14/2012,01 Discharged to home or self care,14AUG2012,24AUG2010,
PT000000099,610952547,Inpatient,03/21/2010,03/23/2010,01 Discharged to home or self care,21MAR2010,23MAR2010,
PT000000132,610888961,Observation patient,11/27/2009,11/27/2009,Not recorded,27NOV2009,27NOV2009,
PT000000159,655709236,Emergency patient,01/22/2007,01/22/2007,01 Discharged to home or self care,22JAN2007,22JAN2007,
PT000000159,655709237,Emergency patient,03/30/2007,03/31/2007,Not recorded,30MAR2007,22JAN2007,
PT000000159,655709238,Emergency patient,04/29/2007,04/30/2007,01 Discharged to home or self care,29APR2007,31MAR2007,
PT000000159,655709239,Emergency patient,05/06/2007,05/06/2007,01 Discharged to home or self care,06MAY2007,30APR2007,
PT000000159,655709240,Observation patient,08/31/2007,08/31/2007,Not recorded,31AUG2007,06MAY2007,
PT000000159,655709241,Emergency patient,09/17/2007,09/17/2007,01 Discharged to home or self care,17SEP2007,31AUG2007,
PT000000159,655709242,Emergency patient,04/30/2008,04/30/2008,01 Discharged to home or self care,30APR2008,17SEP2007,
PT000000159,655709243,Emergency patient,08/06/2008,08/06/2008,01 Discharged to home or self care,06AUG2008,30APR2008,
PT000000159,655709244,Inpatient,09/17/2008,09/17/2008,01 Discharged to home or self care,17SEP2008,06AUG2008,
PT000000159,655709245,Emergency patient,11/30/2008,11/30/2008,01 Discharged to home or self care,30NOV2008,17SEP2008,
PT000000159,655709246,Emergency patient,12/12/2008,12/12/2008,01 Discharged to home or self care,12DEC2008,30NOV2008,
PT000000159,655709247,Emergency patient,01/03/2009,01/03/2009,01 Discharged to home or self care,03JAN2009,12DEC2008,
PT000000159,655709248,Emergency patient,01/09/2009,01/09/2009,Not recorded,09JAN2009,03JAN2009,
PT000000159,655709249,Emergency patient,02/28/2009,02/28/2009,01 Discharged to home or self care,28FEB2009,09JAN2009,
PT000000159,655709250,Emergency patient,05/09/2009,05/10/2009,01 Discharged to home or self care,09MAY2009,28FEB2009,
PT000000159,655709251,Emergency patient,05/19/2009,05/19/2009,01 Discharged to home or self care,19MAY2009,10MAY2009,
PT000000159,655709252,Emergency patient,09/09/2009,09/10/2009,Not recorded,09SEP2009,19MAY2009,
PT000000159,655709253,Emergency patient,11/20/2009,11/20/2009,01 Discharged to home or self care,20NOV2009,10SEP2009,
PT000000159,655709254,Emergency patient,02/16/2010,02/16/2010,01 Discharged to home or self care,16FEB2010,20NOV2009,
PT000000159,655709255,Emergency patient,04/11/2010,04/11/2010,Not recorded,11APR2010,16FEB2010,
PT000000159,655709256,Emergency patient,06/12/2010,06/12/2010,07 Left against medical advice,12JUN2010,11APR2010,
PT000000159,655709257,Emergency patient,06/29/2010,06/29/2010,Not recorded,29JUN2010,12JUN2010,
PT000000159,655709258,Emergency patient,07/13/2010,07/13/2010,01 Discharged to home or self care,13JUL2010,29JUN2010,
PT000000159,655709259,Emergency patient,08/07/2010,08/07/2010,01 Discharged to home or self care,07AUG2010,13JUL2010,
PT000000159,655709260,Emergency patient,08/23/2010,08/23/2010,Not recorded,23AUG2010,07AUG2010,
PT000000159,655709261,Emergency patient,11/22/2010,11/23/2010,01 Discharged to home or self care,22NOV2010,23AUG2010,
PT000000159,655709262,Emergency patient,01/25/2011,01/26/2011,01 Discharged to home or self care,25JAN2011,23NOV2010,
PT000000159,655709263,Emergency patient,05/01/2011,05/01/2011,Not recorded,01MAY2011,26JAN2011,
PT000000159,655709264,Emergency patient,08/19/2011,08/19/2011,01 Discharged to home or self care,19AUG2011,01MAY2011,
PT000000159,655709265,Emergency patient,05/07/2012,05/07/2012,01 Discharged to home or self care,07MAY2012,19AUG2011,
PT000000159,655709266,Emergency patient,11/05/2012,11/06/2012,01 Discharged to home or self care,05NOV2012,07MAY2012,
PT000000159,655709223,Inpatient,06/03/2007,06/07/2007,01 Discharged to home or self care,03JUN2007,07JUN2007,
PT000000159,655709224,Inpatient,01/20/2008,01/21/2008,01 Discharged to home or self care,20JAN2008,07JUN2007,
PT000000159,655709225,Inpatient,10/24/2010,10/26/2010,01 Discharged to home or self care,24OCT2010,21JAN2008,
PT000000159,655709226,Observation patient,12/14/2009,12/14/2009,01 Discharged to home or self care,14DEC2009,14DEC2009,
PT000000225,611184911,Inpatient,06/29/2012,07/01/2012,06 Discharged/transferred to home,29JUN2012,01JUL2012,
PT000000225,611184912,Inpatient,10/05/2012,10/09/2012,01 Discharged to home or self care,05OCT2012,01JUL2012,
PT000000334,682072164,Emergency patient,06/08/2009,06/08/2009,Not recorded,08JUN2009,08JUN2009,
PT000000334,682072163,Inpatient,06/09/2009,06/10/2009,06 Discharged/transferred to home,09JUN2009,10JUN2009,
PT000000337,697620729,Emergency patient,12/03/2010,12/03/2010,01 Discharged to home or self care,03DEC2010,03DEC2010,
PT000000337,697620730,Emergency patient,12/10/2010,12/10/2010,01 Discharged to home or self care,10DEC2010,03DEC2010,
PT000000378,682406411,Emergency patient,01/24/2008,01/24/2008,Not recorded,24JAN2008,24JAN2008,
;;;;

charlesj