proc report

Reply
Contributor
Posts: 44

proc report

ParentChildspendtarget1target_achieved
AA1$83,291.08$249,537.0033.38%
BB1$316,807.45$415,679.0076.21%
CC1$3,846.85$13,500.0028.50%
BB2$420,545.73$277,119.00151.80%
CC2$26,833.02$14,076.00

190.60%

I have the above table and i am planning to get an output like the below ,using proc report ,however i am unable to get the Total % correct.

The output format has to be like the below:

ParentChildtarget1spendtarget_achieved
AA1$249,537.00$83,291.0833%
A $249,537.00$83,291.0833%
BB1$316,807.45$415,679.00131%
B2$420,545.73$277,119.0066%
B $737,353.18$692,798.0094%
CC1$3,846.85$13,500.00351%
C2$26,833.02$14,076.0052%
C $30,679.87$27,576.0090%

any ideas will be great...

Super User
Posts: 9,681

Re: proc report

Post it here ODS and Base Reporting

Super User
Super User
Posts: 7,401

Re: proc report

Whilst its possible to do this type of thing in proc report, I tend to calculate items in a datastep prior to the proc report, and create a dataset with all required sums etc. then a simple proc report of that.

Trusted Advisor
Posts: 1,128

Re: proc report

i agree with , if you think it is fine then you could try the below code. I guess the percentage calculated in the target achieved under expected output given by you are incorrect. I hope the percentage is calculated as spend/target1*100

data have;

input Parent$ Child$ spend:dollar10.2 target1:dollar10.2 target_achieved Smiley Tongueercent5.;

format spend target1 dollar10.2 target_achieved percent10.;

cards;

A A1 $83,291.08 $249,537.00 33.38%

B B1 $316,807.45 $415,679.00 76.21%

C C1 $3,846.85 $13,500.00 28.50%

B B2 $420,545.73 $277,119.00 151.80%

C C2 $26,833.02 $14,076.00 190.60%

;

proc sort data=have;

by parent child;

run;

data want;

set have;

retain spends target1s ;

by parent child;

if first.parent then spends=spend;

else spends+spend;

if first.parent then target1s=target1;

else target1s+target1;

output;

if last.parent and spends ne . then do;

spend=spends;

target1=target1s;

target_achieved=((spend/target1)*100)/100;

child='';

output;

end;

drop target1s spends;

run;

Thanks,

Jag

Thanks,
Jag
Ask a Question
Discussion stats
  • 3 replies
  • 216 views
  • 0 likes
  • 4 in conversation