Incorporating crosstabs with proc report

[hjjijkkl]

Please I need help on this.

How can I use a proc report with cross to create a table below. Please notice the numbers on the table are just examples so, ignore the numbers 

		Test
variable	Category	Arm_1	Arm_2	Total
		N=80	N=90	N=170
sex	Female	1/45 (2)	3/86 (3.5)	4/131 (3)
	Male	23/23 (100.0)	54/58 (93.1)	80/84 (95.2)
Race	Black	1/45 (2)	3/86 (3.5)	4/131 (3)
	white	6/45 (100.0)	7/86 (93.1)	13/131 (9.9)
	Hispanic	6/45 (2)	9/86 (3.5)	15/131 (11.5)

I tried to use the code below but, it doesnt give me the table i wanted.

proc report data=want nowd headline headskip spacing=4
split="\";
column test gender race (n pctn);
define sex / group "sex";
define race / display "race";
define test / across order=internal "TREATMENT GROUP " ;
run;

[hjjijkkl]

this is the code i used
proc report data=want nowd headline headskip spacing=4
split="\";
column test sex race (n pctn);
define sex / group "sex";
define race / display "race";
define test / across order=internal "TREATMENT GROUP " ;
run;

[ballardw]

Try moving "test" after Race in the Columns statement.

[hjjijkkl]

that doesnt help at all.

[hjjijkkl]

That didnt solve the problem

[hjjijkkl]

That didnt solve the problem

[Cynthia_sas]

Hi:

  A few comments: HEADLINE and HEADSKIP and SPACING=4 are ignored by ODS destinations like RTF, PDF and HTML. Those are all LISTING only options and, based on your screen shot, it looks like you're using RTF or PDF as the output destination.

  Your PROC REPORT code shows TEST as being an ACROSS item, but typically you need to use a comma in your COLUMN statement to indicate that variable values or statistics (like N and PCTN) will be stacked under the ACROSS item. So right now, I don't think that your PROC REPORT code is going to give you what you want.

  Also, PROC REPORT is going to give you a separate column for the N and a separate column for the PCTN underneath each unique value for the TEST variable. So you will NOT, by default, get the classic 99 (9.99%)  that is usually seen on demographic reports.

  This type of demographic report has been asked about several times her in the Forums, you should be able to search for examples.

Cynthia

[hjjijkkl]

I am new to SAS. Thats why am having a problem to solve this issue. Can you please give me an example?

[Cynthia_sas]

Hi:

  There are many different ways to do what you want, This method is just one method. It would be different if you didn't have multiple treatments or multiple columns. It would be different if you needed different statistics.

  First, the data needs to be in a particular structure for my program to work.

  This is the PROC FORMAT and PROC REPORT code that I used:

  But I used N and PCTN for my statistics since I wasn't sure how you derived the numbers you showed in your example table.

Hope this helps.

Cynthia

Here's the code I used:

 
data fakedata;
  length varname $30 varvalue $30 bp_status $15 calcstat $30;
  infile datalines dlm=',' dsd;
  input grpord varname $ varvalue $ BP_Status $ calcstat $ Frequency Percent;
datalines4;
1,"Sex","Female","High","1,148 (22.70)",1148,22.70
1,"Sex","Female","Normal","1,124 (22.23)",1124,22.23
1,"Sex","Female","Optimal","502 (9.93)",502,9.93
1,"Sex","Male","High","1,056 (20.88)",1056,20.88
1,"Sex","Male","Normal","958 (18.94)",958,18.94
1,"Sex","Male","Optimal","269 (5.32)",269,5.32
2,"Chol_Status","Borderline","High","798 (15.78)",798,15.78
2,"Chol_Status","Borderline","Normal","793 (15.68)",793,15.68
2,"Chol_Status","Borderline","Optimal","270 (5.34)",270,5.34
2,"Chol_Status","Desirable","High","456 (9.02)",456,9.02
2,"Chol_Status","Desirable","Normal","634 (12.54)",634,12.54
2,"Chol_Status","Desirable","Optimal","315 (6.23)",315,6.23
2,"Chol_Status","High","High","950 (18.79)",950,18.79
2,"Chol_Status","High","Normal","655 (12.95)",655,12.95
2,"Chol_Status","High","Optimal","186 (3.68)",186,3.68
;;;;
run;

** you should know the N for your data or you could create macro variables;
** to supply the N from other procedures;
 proc format;
  value $bp_f 'High' = "High\(N=2,204)"
              'Normal' = "Normal\(N=2,082)"
			  'Optimal' = "Optimal\(N=771)";
run;

** This PROC REPORT step uses BP_Status where your sample showed the treatment columns. ;
** if you want SEX and CHOL_STATUS to be in the first column of the report, then ;
** the data needs to be restructured as I show above;
** Note that for this fake data, the N and PCTN were calculated by other procedures and then;
** the GRPORD variable was created to ensure that the SEX rows came before the CHOL_STATUS rows;
** in the output. ;
ods pdf file='c:\temp\demographic.pdf';
proc report data=fakedata split='\' spanrows
  style(header)={vjust=b};
  column grpord ('Variable Name' varname) ('Value' varvalue) BP_Status,(calcstat) n;
  define grpord / group noprint;
  define varname / group ' ' order=data style(column)=Header;
  define varvalue / group ' ' order=data;
  define BP_status / across f=$bp_f. 'Blood Pressure Status';
  define calcstat / ' ' style(column)={just=r};
  define n / noprint;
run;
ods pdf close;