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

Solved
Occasional Contributor
Posts: 17

# 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.

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;

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