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

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

mragaa — Wed, 28 Feb 2018 18:10:02 GMT

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.

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

Reeza — Wed, 28 Feb 2018 18:24:18 GMT

Try PROC FREQ instead with the default output data set.

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

Astounding — Wed, 28 Feb 2018 19:10:45 GMT

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;

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

mragaa — Wed, 28 Feb 2018 23:09:06 GMT

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!