🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## Percent of row total without naming each column

Hi All,

I have a table that looks like this:

 yrmo Bin Var1 Var2 Var3 Var4 Row Total 202010 1 3 5 7 3 18 202010 2 4 4 4 6 18 202011 1 6 6 7 8 27 202012 1 5 6 7 8 26

This table was generated using proc transpose, and so it's just a simple pivot.

Yrmo is of course Year-Month, Bin is the group, and then Var1 - Var 4 are the types of status' an account is in. Row Total is the total of the Var1-Var4.

What I need to do is get the %'s each value based on the row's total.
So for example, where yrmo = 202010 and Var1 = 3, it would be 16.67% because 3/18 (18 being the row total) ='s 16.67%.

My problem is that I don't know what Var1 - Var4 will be always, so they can be different each time the program runs depending on the status of an account.
I know that proc tabulate has an option to compute rowpctn, which is helpful, but I can't name the variables because I don't know what they will be.

Hopefully I explained this well enough, any help is greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Jade | Level 19

## Re: Percent of row total without naming each column

Hi @hporter,

Probably less elegant in your case, but a general technique for working with variables without knowing their names is to define arrays. These can be based on "implicit" variable lists like _numeric_ or "explicit" variable lists dynamically created from DICTIONARY.COLUMNS.

Example:

``````data have;
input yrmo Bin Var1 Var2 Var3 Var4 Row_Total;
cards;
202010 1 3 5 7 3 18
202010 2 4 4 4 6 18
202011 1 6 6 7 8 27
202012 1 5 6 7 8 26
;

proc sql noprint;
select name, name into :vars separated by ' ', :pvars separated by ' pct_'
from dictionary.columns
where libname='WORK' & memname='HAVE' & name ~in ('yrmo' 'Bin' 'Row_Total')
order by varnum;
quit;

data want(drop=i);
set have;
array v[*] &vars;
array p[*] pct_&pvars;
do i=1 to dim(v);
p[i]=divide(v[i],row_total);
end;
format pct_: percent9.2;
run;``````

4 REPLIES 4
Super User

## Re: Percent of row total without naming each column

IMO, calculate your percentages before your transpose using proc freq and then transpose it together instead.
Super User

## Re: Percent of row total without naming each column

You may want to provide an actual example data set of the data before transposing. There are things that can be done with single variables that are much harder when forced into multiple variables.

Example with a data set you should have available:

```proc tabulate data=sashelp.class;
class sex age;
tables sex all='All sexes',
(age all='row total')*(n rowpctn)
;
run;```

Which might translate to something like this using the data BEFORE transposing and possibly summarized (you don't say):

```proc tabulate data=have;
class yrmo bin status;
tables yrmo*bin ,
(status all='row total')*(n rowpctn)
;
run;```

This would work for a data set that has yrmo bin and status combinations as one record not a summary data set that looks something like this:

```data have;
input yrmo bin status;
datalines;
1 1 1
1 1 1
2 0 2
2 1 3
1 0 2
1 0 4
3 1 1
3 1 2
3 1 3
;```

Hint: a data step like this, pasted into a text or code box opened with the </> or "running man" icon is the preferred way to show example data so we can write code against something like your data.

I am too lazy to type out extra stuff so the yrmo values are shorter. The behavior would be the same except in the case of an actual date value. Then a format assigned to the date value creates the groups.

Look at the SASHELP.CLASS such as print (there are only 19 records so this is easy).

Jade | Level 19

## Re: Percent of row total without naming each column

Hi @hporter,

Probably less elegant in your case, but a general technique for working with variables without knowing their names is to define arrays. These can be based on "implicit" variable lists like _numeric_ or "explicit" variable lists dynamically created from DICTIONARY.COLUMNS.

Example:

``````data have;
input yrmo Bin Var1 Var2 Var3 Var4 Row_Total;
cards;
202010 1 3 5 7 3 18
202010 2 4 4 4 6 18
202011 1 6 6 7 8 27
202012 1 5 6 7 8 26
;

proc sql noprint;
select name, name into :vars separated by ' ', :pvars separated by ' pct_'
from dictionary.columns
where libname='WORK' & memname='HAVE' & name ~in ('yrmo' 'Bin' 'Row_Total')
order by varnum;
quit;

data want(drop=i);
set have;
array v[*] &vars;
array p[*] pct_&pvars;
do i=1 to dim(v);
p[i]=divide(v[i],row_total);
end;
format pct_: percent9.2;
run;``````

Obsidian | Level 7

## Re: Percent of row total without naming each column

Thank you. I did not know about Dictionary.Columns that's going to be useful in the future.

Appreciate the help, this worked perfectly for me.

Discussion stats
• 4 replies
• 597 views
• 2 likes
• 4 in conversation