topic Help proc report in SAS Procedures

Help proc report

raveena — Fri, 03 Feb 2012 16:48:26 GMT

Hi all,

I have a dataset 'Test' with the columns type, _freq_, _freq_p and change.

Need to calculate the changes for prior count in percentage and in case if

Current count is zero the change should display as 100%.

Example:

Type _freq_ _freq_p

Anc 20 20

PCP 0 30

Spc 5966 5967

Output should be

Type Currentcount Priorcount change

Anc 20 20 0.00%

PCP 0 30 100%

Spc 5966 5967 (0.02%)

I tried the below code,

proc report data=test ls=256 headline headskip split='*' nowd

column type _freq_ _freq_p change;

define _freq_/sum "CURRENT COUNT " width=20 format=comma10. spacing=1;

define _freq_p/sum "PRIOR COUNT " width=20 format=comma10. spacing=1;

define change/computed 'CHANGE %' width=20 format=percent8.2 spacing=1;

compute change;

if _freq_p.sum='' then change='1';

else

change=(_freq_.sum -_freq_p.sum)/_freq_p.sum;

run;

But am not getting an exact answer what am expecting ?

please help me out to fix this issue.

Thanks in Advance.

Help proc report

Cynthia_sas — Fri, 03 Feb 2012 17:27:34 GMT

Hi:

Well, first your COMPUTE block is missing an ENDCOMP; statement. So that could be a problem.

Next, since you define _FREQ_ and _FREQ_P as analysis items using the SUM statistic, they are numeric, but, yet, in your test, you are asking if _FREQ_P.SUM = '' (quote quote)...this IF condition will not be met for 2 reasons, _FREQ_P has a value on every report row (20 on the first row, 30 on the second row and 5967 on the third row of data), so this test will NEVER be true. Therefore, the ELSE condition would be the one always executed. And, _FREQ_P is the Previous Count, not the Current Count.

The column that you have indicated as CURRENT COUNT is _FREQ_, so perhaps your IF statement should be:

IF _FREQ_.SUM = 0 then CHANGE= 1;

You should also assign a number to CHANGE, not a quoted string, or else you will get automatic conversion messages in the LOG when the '1' has to be changed to the number 1, since CHANGE is also numeric.

Also, you might be getting format size messages in the log. If it is possible for your column to have negative percents, you have to make the format big enough to accomodate that. I'd recommend a percent9.2 to be on the safe side.

cynthia

Re: Help proc report

Haikuo — Fri, 03 Feb 2012 17:47:03 GMT

Try this working version of your code, and please note the changes being made:

data have;

infile cards;

input Type $ _freq_ _freq_p;

cards;

Anc 20 20

PCP 0 30

Spc 5966 5967

;

proc report data=have headline headskip split='*' nowd ;

column type _freq_ _freq_p change;

define change/computed 'CHANGE %' width=20 format=percent8.2 spacing=1;

compute change;

if _c2_=0 and _c3_ > 0 then change=1;

else if _c2_=0 and _c3_ < 0 then change=-1;

else if _c2_=0 and _c3_ = 0 then change=0;

else change=(_c3_ -_c2_)/_c2_;

endcomp;

run;

Regards,

Haikuo