DATA Step, Macro, Functions and more

calculate percentage with multiple rows

Accepted Solution Solved
Reply
Regular Contributor
Posts: 160
Accepted Solution

calculate percentage with multiple rows

I have 2 datsets one with s_D_1, s_D_2, s_D_3, s_D_4, s_F_1, s_F_2, s_F_3, s_F_4 and the other dataset has variables n_D and n_F

I need to merge them and calculate precentages with 

= 100*(s/n). for both D and F.

 

Can any one help me in this


Accepted Solutions
Solution
‎04-11-2016 06:07 AM
Super User
Super User
Posts: 7,955

Re: calculate percentage with multiple rows

Your life would be much easier if you normalised your data, i.e. have something like:

S_D  RESULT

1      abc

2      def

...

Further processing of the data would be easier, and this task would be a simple merge and one calculation step.  With your transposed dataset, you will need to use arrays, and the code thus becomes a bit more complicated - not to mention further coding will be harder.  As you have not provided test data (in the form of a datastep) this code is untested:

data want;
  merge base other;  /* Assumes both are sorted by the id variables */
  by <id variables>;  /* update this row with the matching variables in each dataset */
  array s_d_{4};
  array s_f_{4};
  array s_d_results{4};
  array s_f_results{4};
  do i=1 to 4;
    s_d_results{i}=(s_d_{i} / n_d) * 100;
    s_f_results{i}=(s_f_{i} / n_f) * 100;
  end;
run;
 

Again though, far easier to normalise your data.

 

View solution in original post


All Replies
Solution
‎04-11-2016 06:07 AM
Super User
Super User
Posts: 7,955

Re: calculate percentage with multiple rows

Your life would be much easier if you normalised your data, i.e. have something like:

S_D  RESULT

1      abc

2      def

...

Further processing of the data would be easier, and this task would be a simple merge and one calculation step.  With your transposed dataset, you will need to use arrays, and the code thus becomes a bit more complicated - not to mention further coding will be harder.  As you have not provided test data (in the form of a datastep) this code is untested:

data want;
  merge base other;  /* Assumes both are sorted by the id variables */
  by <id variables>;  /* update this row with the matching variables in each dataset */
  array s_d_{4};
  array s_f_{4};
  array s_d_results{4};
  array s_f_results{4};
  do i=1 to 4;
    s_d_results{i}=(s_d_{i} / n_d) * 100;
    s_f_results{i}=(s_f_{i} / n_f) * 100;
  end;
run;
 

Again though, far easier to normalise your data.

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 179 views
  • 0 likes
  • 2 in conversation