topic Re: Calculate percentage of subset from total set in New SAS User

Calculate percentage of subset from total set

victhor — Tue, 22 Sep 2020 16:22:48 GMT

Hello Everyone

Probably another simple question but I am stuck.

I have a data set that looks something like the following:

id	fabric color	location	price	fiveormore
1	blue	store	$ 4.99	no
2	black	warehouse	$ 6.00	yes
3	red	warehouse	$ 2.00	no
4	blue	store	$ 10.00	yes
5	black	store	$ 7.50	yes
6	red	warehouse	$ 10.00	yes
7	blue	store	$ 5.00	yes
8	black	warehouse	$ 3.00	no
9	red	store	$ 1.00	no
10	blue	store	$ 5.00	yes

What I am trying to tabulate the count of each color that are say $5.00 or more per location which I can do pretty easily but I need another column that is the percentage of the grand total of the color regardless of location.

Something like:

	Store		Warehouse
color	count	%	count	%
Blue	3	75.0	0	0
Black	1	33.3	1	33.3
Red	0	0	1	33.3

I have done something like below but, obviously, it is not really giving me what I want.

PROC FORMAT;
picture pctf (round) other='09.9';
RUN;

proc tabulate
data = birth.lowbirthweight17;
class color location;
table county, location*(n=' ' pctn<color>*f=pctf.);
where fiveormore = 'Yes';
run;

I just can't quite figure out how to do it. Any help would be greatly appreciated.

Re: Calculate percentage of subset from total set

PaigeMiller — Tue, 22 Sep 2020 16:26:10 GMT

What is the denominator of the percent? How do you get 75% for the blue/store combination?

Re: Calculate percentage of subset from total set

Reeza — Tue, 22 Sep 2020 16:29:09 GMT

Assuming your data is just a sample and the results are not actually the numbers you expect this should answer your question:
https://documentation.sas.com/?docsetId=proc&docsetTarget=p0vdza4puzkcghn1j5xtqfb4sahr.htm&docsetVersion=9.4&locale=en

Re: Calculate percentage of subset from total set

victhor — Tue, 22 Sep 2020 16:58:51 GMT

In the table there are total of 4 units of blue.

Re: Calculate percentage of subset from total set

Reeza — Tue, 22 Sep 2020 17:01:14 GMT

But your table only counts 3??????
Where does the other 1 disappear to and why then?

Re: Calculate percentage of subset from total set

PaigeMiller — Tue, 22 Sep 2020 19:33:40 GMT

Here is some UNTESTED code for you, since we can't work from screen captures (if you want tested code, we'd need the actual data as a SAS data step). If you make a binary variable that has numeric values 0 and 1 (instead of character values yes and no) then the math becomes much simpler. You probably don't even need PROC SUMMARY here, you could probably do it all in PROC REPORT, but that's the way I do it.

data have2;
    set have(drop=fiveormore);
    above5=(price>=5);
run;
proc summary nway data=have2;
    class fabric_color location;
    var above5;
    output out=stats sum=count mean=percent;
run;
proc report data=stats;
    columns fabric_color location,(count percent);
    define fabric_color/group;
    define location/across;
    define count/sum;
    define percent/sum;
run;

Re: Calculate percentage of subset from total set

victhor — Tue, 22 Sep 2020 17:15:08 GMT

I will give that a try. Thanks for your time and your assistance.