topic Re: Percent of total based on matching column values in New SAS User

Percent of total based on matching column values

aya_h — Mon, 11 Mar 2024 15:28:13 GMT

New to SAS -- I know this is probably silly to most of you but it's taken me hours. I have a dataset that looks like this:

COMMODITY	COUNTRY	QUANTITY1	QUANTITY2	QUANTITY3
010	ARGENTINA	2	8	3
010	ANDORRA	3	10	2
010	TOTAL FOR ALL COUNTRIES	5	18	5
020	BELGIUM	10	6	30
020	RUSSIA	15	4	20
020	TOTAL FOR ALL COUNTRIES	25	10	50

There are many commodity codes, countries, and quantities, so doing it manually would be a pain. I am trying to use Quantity 1 values as a base quantity (so the others should be untouched) to generate a percentage report to see which country generates what % of world total quantity for a given commodity code.

Ideally that would be represented in a different column like so:

COMMODITY	COUNTRY	QUANTITY1	% of total	QUANTITY2	QUANTITY3
010	ARGENTINA	2	40%	8	3
010	ANDORRA	3	60%	10	2
010	TOTAL FOR ALL COUNTRIES	5	100%	18	5
020	BELGIUM	15	50%	6	30
020	RUSSIA	15	50%	4	20
020	TOTAL FOR ALL COUNTRIES	30	100%	10	50

Later in my program, the rows that do not exceed 3% will be omitted from the final dataset, so I need this column to be permanent.

Thank you so much!

Re: Percent of total based on matching column values

SASJedi — Mon, 11 Mar 2024 15:54:54 GMT

How about something like this?

data have;
infile datalines dsd dlm='|';
input COMMODITY	COUNTRY:$25. QUANTITY1	QUANTITY2	QUANTITY3;
datalines;
010|ARGENTINA|2|8|3
010|ANDORRA|3|10|2
010|TOTAL FOR ALL COUNTRIES|5|18|5
020|BELGIUM|10|6|30
020|RUSSIA|15|4|20
020|TOTAL FOR ALL COUNTRIES|25|10|50
;

proc sql;
create table want as
select have.*, quantity1/total as Pct '% of Total' format=percent7.
	from have
		inner join
	(select commodity, quantity1 as Total from have where Country = 'TOTAL FOR ALL COUNTRIES') as tot
	on have.commodity=tot.commodity
;
select * from want;
quit;

Result:

COMMODITY	COUNTRY	QUANTITY1	QUANTITY2	QUANTITY3	% of Total
10	ARGENTINA	2	8	3	40%
10	ANDORRA	3	10	2	60%
10	TOTAL FOR ALL COUNTRIES	5	18	5	100%
20	BELGIUM	10	6	30	40%
20	RUSSIA	15	4	20	60%
20	TOTAL FOR ALL COUNTRIES	25	10	50	100%

Re: Percent of total based on matching column values

ballardw — Mon, 11 Mar 2024 16:07:08 GMT

First, please provide example data in the form of data step code pasted into a text or code box opened by clicking on the </> or "running man" icon above the message box. Example:

data have;
   input commodity $ Country :$10. quantity1 quantity2 quantity3;
datalines;
010	ARGENTINA	2	8	3
010	ANDORRA	3	10	2
020	BELGIUM	10	6	30
020	RUSSIA	15	4	20
;

Second, including intermediate summary rows like your Total for commodity actually makes it harder in many respects because that row of data needs to be treated differently. So I have excluded those.

If I understand what you want this may be one way (using the above data set)

proc tabulate data=have out=example;
   class commodity country;
   var quantity: ;
   tables commodity,
         (country All='All countries'),
          quantity: *(sum='Number'*f=best5. pagepctsum ='% of total')
   ;
run;

The use of Quantity: above is a list generator and will use all the variables whose names start with quantity.

The above code creates a table for each commodity in the results window. The Out= option on the proc statement does create a data set with the summary values though it has a bit of extra info.

The Country value will be blank where where the _type_ variable is 10, which means that it is the "All countries" row.

Proc Tabulate requires a statistic for your quantity variable and Sum is the choice so the All countries row has a total. The output data set will have these in the Quantity1_sum, Quantity2_sum (continue the pattern).

The percents will be in variables named Quantity1_pctsum_10_quantity1 (the 10 is the same as the _type_ to show which total was used as the denominator).

Since you didn't provide any example of data where the % do not exceed 3 or show what you expect to create when that occurs I can't help past that.

It may be because of "delete row" rule that this might make an easier to use data set:

proc tabulate data=have out=example;
   class commodity country;
   var quantity: ;
   tables commodity,
         (country All='All countries'),
          quantity1 *(sum='Number'*f=best5. pagepctsum ='% of total')
   ;
   tables commodity,
         (country All='All countries'),
          quantity2 *(sum='Number'*f=best5. pagepctsum ='% of total')
   ;
   tables commodity,
         (country All='All countries'),
          quantity3 *(sum='Number'*f=best5. pagepctsum ='% of total')
   ;
run;

As each quantity variable will have a separate block of values (rows) to work with.