topic Percent of row total without naming each column in SAS Programming

Percent of row total without naming each column

hporter — Fri, 07 May 2021 16:03:53 GMT

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.

Re: Percent of row total without naming each column

Reeza — Fri, 07 May 2021 16:18:19 GMT

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

Re: Percent of row total without naming each column

ballardw — Fri, 07 May 2021 16:54:40 GMT

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).

Re: Percent of row total without naming each column

FreelanceReinh — Fri, 07 May 2021 18:23:00 GMT

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;

Re: Percent of row total without naming each column

hporter — Fri, 07 May 2021 19:30:05 GMT

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.