Help using Base SAS procedures

How to get each member's % of their group total in a new variable?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How to get each member's % of their group total in a new variable?

Hi everyone,

 

The challenge: I have the sales and region of each store. I wanna calculate each store's percentage sales of its region's total and add this result as a new variable in the dataset.

 

What I have so far: I'm assuming this is solvable by PROC SQL yet, as you can probably guess  now, my SQL skills are newbie-level. I only got this far (from another thread here):

 

PROC SQL;

SELECT Region, COUNT(storeID), SUM(Sales)
FROM sample 
GROUP BY Region
ORDER BY Region;

QUIT;

 

Any help would be much appreciated.

 

I'm attaching a sample of the data for your convenience. I'm using SAS 9.4.

 

 

Attachment

Accepted Solutions
Solution
‎02-28-2018 06:09 PM
Super User
Posts: 6,785

Re: How to get each member's % of their group total in a new variable?

Since you are adding the variable to the data set, I would go with a DATA step.  As long as each store has a single observation, there are no complications. 

 

proc sort data=have;

by region;

run;

 

data want;

tot_sales=0;

do until (last.region);

   set have;

   by region;

   tot_sales + sales;

end;

do until (last.region);

   set have;

   by region;

   pct_sales = sales / tot_sales;

   output;

end;

drop tot_sales;

run;

View solution in original post


All Replies
Super User
Posts: 23,771

Re: How to get each member's % of their group total in a new variable?

Try PROC FREQ instead with the default output data set.

Solution
‎02-28-2018 06:09 PM
Super User
Posts: 6,785

Re: How to get each member's % of their group total in a new variable?

Since you are adding the variable to the data set, I would go with a DATA step.  As long as each store has a single observation, there are no complications. 

 

proc sort data=have;

by region;

run;

 

data want;

tot_sales=0;

do until (last.region);

   set have;

   by region;

   tot_sales + sales;

end;

do until (last.region);

   set have;

   by region;

   pct_sales = sales / tot_sales;

   output;

end;

drop tot_sales;

run;

Occasional Contributor
Posts: 17

Re: How to get each member's % of their group total in a new variable?

Posted in reply to Astounding

Thank you @Astounding and @Reeza , for your time and brain cycles.

 

@Astounding  Awesome! I really appreciate that you wrote this beautiful code. It works fine and I'll spend some time "studying" it. Thank you.

 

@ReezaThank you for this suggestion. I'll definitely look into it while reverse engineering Astounding's code.

 

 

My respects!

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 180 views
  • 2 likes
  • 3 in conversation