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

[mragaa]

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.

[Reeza]

Try PROC FREQ instead with the default output data set.

[Astounding · Accepted solution]

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;

[mragaa]

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!