DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-01-2017 06:20 AM
Super User
Posts: 10,538

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

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;

View solution in original post


All Replies
Super User
Posts: 17,907

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

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

A+ Smiley Wink

 

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;

 

 

 

Occasional Contributor
Posts: 10

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

Hi There,

 
Thanks for reply Smiley Happy 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?

How I want the output.PNG
Super User
Posts: 17,907

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


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. 

 

Solution
‎02-01-2017 06:20 AM
Super User
Posts: 10,538

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

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;
Occasional Contributor
Posts: 10

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 136 views
  • 1 like
  • 3 in conversation