topic Re: Issue with picking the final result of cumulative average and then using it for further computat in SAS Programming

Issue with picking the final result of cumulative average and then using it for further computation

GurmeetKaur_23 — Tue, 31 Jan 2017 21:22:44 GMT

Hi All,

I have a base dataset on which I am performing computation as:

data one;
input name$ sal;
datalines;
A 100
B 200
C 300
D 400
E 500
;
run;

Now, I want to calculate as:

data two;
set one;
array x(*) sal;
do i = 1 to dim(x);
mypercentage = x{i}*100/[CUMULATIVE TOTAL OF SAL FROM DATASET ONE];
end;
run;
proc print;
run;

My issue is: I am able to run this dataset two and getting proper result if I am putting 2( just a random number) instead of the CUMULATIVE TOTAL OF SAL FROM DATASET ONE.

I want to use the cumulative total of sal from dataset one and use it in dataset two which will tell like 100 is 6.66 percent of 1500 (cumulative total of sal from dataset one) and so on for every iteration.

Please suggest, how is it possible. I hope, I am clear with my question.

Re: Issue with picking the final result of cumulative average and then using it for further computat

Reeza — Tue, 31 Jan 2017 21:40:50 GMT

Your question is relatively clear, thanks for posting for sample data and the code you have so far!

A+ 😉

So, here's how to do what you want, though I'm not sure what your array is trying to do. I think you're trying to calculate the percent of total, so I'm going with that. If not, please clarify.

data one;
input name$ sal;
datalines;
A 100
B 200
C 300
D 400
E 500
;
run;

proc means data=one noprint nway;
output out=tot_val sum(sal)=tot_sal;
run;

data two;
set one;
if _n_ = 1 then set tot_val (drop = _type_ _freq_);
percent = sal/tot_sal;
format percent percent12.1;
run;

proc print data=two;
run;

And an alternative method, if I guessed correctly:

*Another way without the multiple steps;
proc freq data=one;
table name/out=three outcum outpct;
weight sal;
run;

Re: Issue with picking the final result of cumulative average and then using it for further computat

GurmeetKaur_23 — Tue, 31 Jan 2017 21:54:27 GMT

Hi There,

Thanks for reply 🙂 but I want something that I have shown in the excel sheet and that too using arrays.

Can you help me resolve that issue via using arrays please?

Re: Issue with picking the final result of cumulative average and then using it for further computat

Reeza — Tue, 31 Jan 2017 21:56:55 GMT

@GurmeetKaur_23 wrote:

Hi There,

Can you help me resolve that issue via using arrays please?

The results are similar to what you have in Excel, which part of the code/results suggested doesn't work for what you want?

No, you can't use arrays. In SAS arrays work on a single row, not on columns. They're considered a shortcut reference to variables but not an array in the sense of matrix/arrays. If you really want to use arrays, you'll need to use IML and matrix language.

Re: Issue with picking the final result of cumulative average and then using it for further computat

ballardw — Tue, 31 Jan 2017 23:37:55 GMT

You might want a report procedure as the kind of percentages you are using can lead to confusion when used as input into other processes.

Please look at

proc tabulate data=one;
   class name;
   var sal;
   table name all=total,
         sal*(sum='Sal total' colpctsum="Percent of column total");
run;

Re: Issue with picking the final result of cumulative average and then using it for further computat

GurmeetKaur_23 — Wed, 01 Feb 2017 11:22:28 GMT

Thank you ballardw. Thanks a lot for helping me with the fact that "No, you can't use arrays. In SAS arrays work on a single row, not on columns. They're considered a shortcut reference to variables but not an array in the sense of matrix/arrays. If you really want to use arrays, you'll need to use IML and matrix language. "

Also, the solution that you gave is working as expected. Really, thanks a lot.