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


Hi All,

 

I have a table that looks like this:

yrmoBinVar1Var2Var3Var4

Row Total

202010

1357318
2020102444618
2020111667827
2020121567826

 

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
FreelanceReinh
Jade | Level 19

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;

 

 

View solution in original post

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

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

FreelanceReinh
Jade | Level 19

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;

 

 

hporter
Obsidian | Level 7

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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