Help proc report

[raveena]

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.

[Cynthia_sas]

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

[Haikuo]

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