The SAS Output Delivery System and reporting techniques

Create MISSING VALUE in PROC REPORT

Reply
Contributor
Posts: 22

Create MISSING VALUE in PROC REPORT

Hello,

I want Create the following rable with PROC REPORT. I am taking data from two data set and merge with each other.

Data X;

Merge ABC(in=a) CDE(in=B);

By VAR;

If a or b then do;

end;

output;

run;

proc format;

value $relation 'M'='MALE'

'F'='FEMALE';

run;

PROC REPORT DATA = X nowd split='*';

COLUMN DESCRIPTION (total_COUNT Remain_COUNT PCT) (RELATIONSHIP, (COUNT REMAIN PCT_CUM));

DEFINE DESCRIPTION/Display;

DEFINE total_COUNT/Analysis sum;

DEFINE Remain_COUNT/Analysis sum;

DEFINE PCT / "%" Computed f=Percent4.;

DEFINE RELATIONSHIP/ACROSS f=$relation.;

DEFINE COUNT/Analysis sum;

DEFINE REMAIN/Analysis sum;

DEFINE PCT_CUM/Computed f=Percent4.;

Compute PCT;

PCT=Remain_COUNT.sum/total_COUNT;

endcomp;

Compute PCT_CUM;

PCT_CUM=REMAIN.sum/COUNT;

endcomp;

rbreak after /summarize skip suppress;

Compute after;

DESCRIPTION="GRANDTOTAL";

PCT=Remain_COUNT.sum/total_COUNT;

PCT_CUM=REMAIN.sum/COUNT;

Call Define(_ROW_,STYLE);

endcomp;

run;

DESIRE TABLE:

Untitled.png

When I run this code then PCT_CUM column having missing '.'  value without any error.  Should I have to make two separate count and columns for relationship?

Thanks.

SAS Super FREQ
Posts: 8,868

Re: Create MISSING VALUE in PROC REPORT

Posted in reply to patel1987

Hi:

  When you use ACROSS items and are trying to compute a variable (such as PCT_CUM) that is under the ACROSS item, you cannot reference the computed items by their "simple" name ... instead you have to use an absolute column number in your COMPUTE block (such as _C1_, _C2_, etc).

  There have been many, many previous forum posting on using ACROSS items in PROC REPORT and computing items under an ACROSS item. I would suggest that you search the forum for some of these previous postings. I know that there are code examples that have already been posted.

  In order to see what I mean by absolute column numbers, run this simple PROC REPORT code, but then, look at the output dataset and the absolute column numbers that have been internally assigned by PROC REPORT. Every time you use an ACROSS item in PROC REPORT code, the absolute column numbers are internally assigned, based on the number of unique values for the ACROSS variable. This means that you MUST use the absolute column numbers in your COMPUTE block.

cynthia

The program:

ods listing close;                                                      

ods html file='c:\temp\show_absolute.html'   

    style=sasweb;     

proc report data=sashelp.class nowd

            out=work.absolute_cols;

  title 'Proc Report Report';

  column age sex,(height weight diff); 

  define age / group; 

  define sex / across;    

  define height / mean; 

  define weight / mean;

  define diff / computed;

  compute diff;

      _c4_ = _c2_ - _c3_;                                  

      _c7_ = _c5_ - _c6_;

  endcomp;

run;

proc print data=work.absolute_cols; 

  title 'What Absolute Columns Looks Like';

run;

ods html close;

title;

Ask a Question
Discussion stats
  • 1 reply
  • 186 views
  • 0 likes
  • 2 in conversation