topic Re: Distinct count of subjects by multiple class variables for cross-tabulation in SAS Programming

Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sat, 27 Apr 2019 22:49:33 GMT

Hi folks,

I'm trying to create a table shown in image which requires distinct counts of patients by class variables (enrolled and cancer and sites). Simple proc freq with class would do if my data was a row per patient. However, my data is repeated and proc sql using distinct functon shown in the code didn't cross-tabulate for me.

I appreciate your time for help. Thanks in advance.

My data has:

enrolled: constant variable for each subjects. takes 0,1. if someone enrolled then enrolled (1) or not enrolled (0) across all rows for each patient

cancer: a dummy variable (0,1). One patient can take both 0 or 1 for different rows.

sites: distinct categorical variable for each patient. one patient has only one site.

/*failed codes*/
proc freq data=have nlevels;
class sites;
tables a*b/norow nocol nopercent nocum;
run; 

proc sql; 
SELECT count(distinct ID) as N_PATIENTS,
       CANCER, ENROLLED, SITES 
from HAVE
GROUP BY ENROLLED, CANCER, SITES; 
quit;

/*mock data*/

DATA HAVE;
INPUT ID CANCER enrolled SITES $;
CARDS;
1 0 1 Lung
1 1 1 Lung
2 0 1 Liver
2 0 1 Liver
2 1 1 Liver
3 0 1 Lung
3 1 1 Lung
4 0 1 Cervix
4 0 1 Cervix
4 1 1 Cervix
5 0 0 Anus
6 0 1 Thyroid
6 1 1 Thyroid
7 0 1 CNS
7 1 1 CNS
8 0 1 Prostate
8 1 1 Prostate
8 1 1 Prostate
9 0 1 Breast
9 1 1 Breast
10 0 1 Breast
10 1 1 Breast
11 0 0 Thyroid
11 0 0 Thyroid
11 0 0 Thyroid
12 0 1 Kidney
12 1 1 Kidney
13 0 1 CNS
13 0 1 CNS
13 1 1 CNS
14 0 1 Prostate
14 1 1 Prostate
;

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Reeza — Sat, 27 Apr 2019 22:07:58 GMT

It's a double proc freq for distinct counts.

Do it the first time with the variable you want to count included and then a second time without. Except it doesn't seem like you're actually counting distinct based on the WANT data set. Can you explain why there is no entries/counts for Cancer=0 and Enrolled=1 when you have those in the data? Are you using the assumption that if you have cancer/enrolled that needs to be kept at all? If so, take the max for each cancer/enrolled combo then do the proc freq.

data have;
input ID CANCER ENROLLED;
cards;
1   1   1
1   1   1
1   1   1
1   1   1
1   0   1
1   0   1
1   0   1
1   0   1
2   1   0
2   1   0
2   1   0
2   1   0
2   0   0
2   0   0
2   0   0
2   0   0
;

*distinct counts
proc freq data=have noprint;
table cancer*enrolled*id /out = temp;run;

proc freq data=temp;
table cancer*enrolled / out=want;
run;


*what you likely actually want;
proc sql;
create table temp2 as
select id, max(cancer) as cancer, max(enrolled) as enrolled
from have
group by ID;
quit;

proc freq data=temp2;
table cancer*enrolled;
run;

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sat, 27 Apr 2019 22:14:50 GMT

@Reeza

I was just staring at this forum where you offered similar appraoch. Let me look at your suggestion to my post here. I'll get back to you asap. Thanks for responding me on weekend!

https://communities.sas.com/t5/SAS-Programming/PROC-FREQ-with-nodupkey/td-p/389788

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sat, 27 Apr 2019 22:39:41 GMT

@Reeza

Reeza thanks for a pointer. I have all combinations of cancer*enrolled. Attached image is the desired table.

DATA HAVE;
INPUT ID CANCER	enrolled SITES $;
CARDS;
1	0	1	Lung
1	1	1	Lung
2	0	1	Liver
2	0	1	Liver
2	1	1	Liver
3	0	1	Lung
3	1	1	Lung
4	0	1	Cervix
4	0	1	Cervix
4	1	1	Cervix
5	0	0	Anus
6	0	1	Thyroid
6	1	1	Thyroid
7	0	1	CNS
7	1	1	CNS
8	0	1	Prostate
8	1	1	Prostate
8	1	1	Prostate
9	0	1	Breast
9	1	1	Breast
10	0	1	Breast
10	1	1	Breast
11	0	0	Thyroid
11	0	0	Thyroid
11	0	0	Thyroid
12	0	1	Kidney
12	1	1	Kidney
13	0	1	CNS
13	0	1	CNS
13	1	1	CNS
14	0	1	Prostate
14	1	1	Prostate
;

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Reeza — Sat, 27 Apr 2019 22:36:58 GMT

You need to answer this question based on your smaller example: Can you explain why there is no entries/counts for Cancer=0 and Enrolled=1 when you have those in the data?

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sat, 27 Apr 2019 22:40:54 GMT

smaller example is a bad example which doesn't reflect my true data. i have enrolled 0,1 vs cancer 0,1 this all combinations of these.

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sat, 27 Apr 2019 22:50:34 GMT

Reeza, I edited my post. Now mock data reflects my actual data.

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Reeza — Sun, 28 Apr 2019 02:04:55 GMT

One of the options I posted should work then. Do they not? If not, how so?

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sun, 28 Apr 2019 02:16:44 GMT

Reeza, none of them give me an organized table shown in image. I'm wondering about combining the first proc freq with proc tabulate instead the second proc freq. What do you think?

Re: Distinct count of subjects by multiple class variables for cross-tabulation

smijoss1 — Sun, 28 Apr 2019 15:45:38 GMT

This looks like a simple case of 1 proc tabulate using define across

Re: Distinct count of subjects by multiple class variables for cross-tabulation

smijoss1 — Sun, 28 Apr 2019 16:36:02 GMT

Thank @Cynthia_sas

https://communities.sas.com/t5/SAS-Programming/PROC-Tabulate-displaying-counts-and-percentages-with-multiple/td-p/319554

proc format;
value   Cancer_Fmt  
0 = 'Cancer=0'
1 = 'Cancer=1' ;


value   Enroll_Fmt  
0 = 'Enrolled=0'
1 = 'Enrolled=1' ;

picture ColPCnt other='009.99%';
run;

proc tabulate data=HAVE;
class sites cancer enrolled;
table sites  all, all*(n='#' colpctn='Col%'*f=ColPCnt.) CANCER='' * (all*(n='#' colpctn='Col%'*f=ColPCnt.) enrolled='' * (n='#' colpctn='Col%'*f=ColPCnt.)) ;
format cancer Cancer_Fmt. enrolled Enroll_Fmt.;
Run;

output :

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sun, 28 Apr 2019 16:54:21 GMT

@smijoss1 @Cynthia_sas @Reeza

Thank you very much!!!! I greatly appreciate your time.

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sun, 28 Apr 2019 17:20:30 GMT

@smijoss1

Applied neatly to my actual data. May I ask one more question please? Do you know how to control the order of columns appear in the table by any chance?

Re: Distinct count of subjects by multiple class variables for cross-tabulation

smijoss1 — Sun, 28 Apr 2019 17:50:12 GMT

What is your order

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sun, 28 Apr 2019 18:17:22 GMT

@smijoss1

as shown in the image below

Re: Distinct count of subjects by multiple class variables for cross-tabulation

smijoss1 — Sun, 28 Apr 2019 18:30:02 GMT

read up on proc tabulate order=formatted.

class sites cancer enrolled / order=formatted;

OR 

class sites ;
class cancer / order=formatted;
class enrolled / order=formatted;

Re: Distinct count of subjects by multiple class variables for cross-tabulation

Cruise — Sun, 28 Apr 2019 18:59:33 GMT

Thank you. The 1st option did the trick.