Data from Proc Freq to Proc Report

[DmytroYermak]

Hi all,

Could you please help with the code of Proc Report. I have the dataset after processing of Proc Freq:

(the code for tes2.sas7bdat is attached).

I need the report in the following view:

Thank you!

[ballardw]

2 more questions you have to answer:

1) what % are you wanted calculated? Row, Column or cell contribution to a table.

2) Since Visit2Finding has two values, N and Y, how do you want the values of Visit2finding used?

Here's one example, but it you do not want any of the Visit2 values used you will have to tell us what you want. Your example data is small enough you could fill in the % by hand for your example data so we have a chance of getting what you want. Note that getting something exactly as you show with imbedded headings(your cohort1 and 2) in the middle of a table is not a trivial exercise.

proc tabulate data=test;
   class examination;
   class cohort;
   class visit1finding visit2finding;
   table examination*visit1finding,
         visit2finding*cohort * rowpctn='';
run;

[DmytroYermak]

I bring my apologies for the misleading. The exact view I need is below. My question is how to use PROC REPORT to generate the summary report. Thank you.

[Cynthia_sas · Accepted solution]

Hi,

  I'm sorry, I still don't entirely understand. You have a total of 8 cells for Abdomen that need filling in for the % and then you have a total of 8 cells for SKIN that need the % filled in. BUT, in your PROC FREQ output, you only have 8 rows. To me, that implies that some % values will appear twice or else that little scribble in some of the cells is a 0%???

Then, the values for Visit2Finding are just N or Y, the values for Cohort are either 1 or 2.

The COMPLETEROWS option in PROC REPORT will produce an "empty" row for Abdomen, if that's what you're showing in the above table. You will need to do a bit of adjustment to have the missing values turned into 0 and then displayed with a % sign, but it is do-able.

BallardW is correct though, that the repeated headings in the middle of the table are not something you could do easily with either PROC REPORT or PROC TABULATE. Also, you do NOT show your final destination of choice. Since it is black and white, that you show, you might want RTF or PDF, or that just might be an artifact of the application you used to take the screen shot. That is unknown. If you used BY Group processing to get one table for Abdomen and another table for Skin, then you'd have some space between the tables, but the column headers would repeat.\

It is possible, but you're going to need to work on studying PROC REPORT and how ACROSS items work and about absolute column numbers.

This is the closest I came up with based on my understanding:

from this code:

data test;
	length Patient $1 Examination $7 Visit1Finding	Visit2Finding Cohort $1;
	infile datalines dlm='	';
	input Patient Examination Visit1Finding	Visit2Finding Cohort;
datalines;
1	SKIN	N	N	1
1	ABDOMEN	N	N	1
2	SKIN	Y	N	2
2	ABDOMEN	N	Y	2
3	SKIN	N	Y	1
3	ABDOMEN	N	Y	1
4	SKIN	N	Y	2
4	ABDOMEN	N	Y	2
5	SKIN	Y	Y	1
5	ABDOMEN	N	Y	1
6	SKIN	N	Y	2
6	ABDOMEN	N	Y	2
7	SKIN	N	Y	1
7	ABDOMEN	N	Y	1
8	SKIN	N	Y	1
8	ABDOMEN	N	Y	1
;
run;
  
 
proc freq data=test noprint;
	tables examination*visit1Finding*visit2Finding*cohort / out=test2 (drop=count) nocum nofreq missing list;
run;
  
proc print data=test2;
run;
  
proc format;
  value $coh '1'='Cohort 1'
             '2'='Cohort 2';
  value $ny 'Y' = 'Y'
            'N' = 'N';
run;

proc sort data=test2;
  by examination;
run;
     
options missing=. nobyline;
ods html file='c:\temp\useby.html';
ods rtf file='c:\temp\useby.rtf' startpage=no;
proc report data=test2 completerows spanrows;
by examination;
  column examination visit1finding percent,cohort,visit2finding;
  define examination/ group
         style(column)={fontweight=bold};
  define visit1finding / group descending f=$ny. preloadfmt;
  define percent / ' ' 
         style(column)={width=.55in};
  define cohort / across f=$coh. 'Visit2Finding';
  define visit2finding / across ' ';
  compute percent;
     if _c3_ = . then _c3_ = 0;
     if _c4_ = . then _c4_ = 0;
     if _c5_ = . then _c5_ = 0;
     if _c6_ = . then _c6_ = 0;
	 call define(_col_,'style','style={posttext="%"}');
  endcomp;
run;
ods html close;
ods rtf close;

cynthia

[DmytroYermak]

Thank you, Cynthia. You are completely right with the interpretation of zero data. Thank you for the code - I have to study it thoroughly.