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

Hi, I am using SAS 9.4. I have data that look like this:

 

Frequency ColPercent Sex Age Region
10 50 Male . .
10 50 Female . .
12 60 . Young .
8 40 . Old .
15 75 . . West
5 25 . . East

 

And I would like it to look like this:

Frequency ColPercent Variable
10 50 Male
10 50 Female
12 60 Young
8 40 Old
15 75 West
5 25 East

 

I know I could do something like: if sex ne . then Variable=sex but I actually have ~100 variables so that would take a long time. Is there a more efficient way accomplish what I want? Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

ods output crosstabfreqs=summary;
proc freq data=sashelp.class;
table sex*(_all_);
run;



data long;
	length variable $32. variable_value $50.;
	set summary;
	Variable=scan(table, 2, '*');
	Variable_Value=strip(trim(vvaluex(variable)));
	presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
	keep sex variable  variable_value frequency percent presentation;
	label variable='Variable' variable_value='Variable Value';
run;

 https://gist.github.com/statgeek/0c4aeec9053cf8050be18a03b842c1b9

View solution in original post

12 REPLIES 12
Tom
Super User Tom
Super User

That is what the COALESCE() function is for. 

 

In a data step you would have to use COALESCEC() for character variables.

data want;
  set have;
  length variable $40;
  variable = coalescec(of sex age region);
run;
Lefty
Obsidian | Level 7

Thanks. When I do the coalesce, it gives me the values of each column, like this:

 

Frequency ColPercent Variable
10 50 1
10 50 0
12 60 0
8 40 1
15 75 1
5 25 0

 

Is there a way to have the text of the variables come out instead?

PaigeMiller
Diamond | Level 26

Please show us the exact code you used.

--
Paige Miller
Tom
Super User Tom
Super User

@Lefty wrote:

Thanks. When I do the coalesce, it gives me the values of each column, like this:

 

Frequency ColPercent Variable
10 50 1
10 50 0
12 60 0
8 40 1
15 75 1
5 25 0

 

Is there a way to have the text of the variables come out instead?


You could use an ARRAY and the VNAME() function.   It will require that the variables are all of the same TYPE to be able to put them into an array.

If you know there is only one non-missing value you can use WHICHx() to find the index into the array.  So if the variables are all numeric use the WHICHN() function.  If they are character use the WHICHC() function.

data want;
 set have;
 array vars x y z;
 name = vname(vars[whichn(coalesce(of vars[*]),of vars[*])]);
run;
Kurt_Bremser
Super User

So you omitted one very important fact about your data: that your variables are in fact numeric, with custom formats attached. To retrieve formatted values, use the VVALUE function.

andreas_lds
Jade | Level 19

I would have a closer look at the step creating the first table, maybe it can be improved to avoid fixing it.

ballardw
Super User

@andreas_lds wrote:

I would have a closer look at the step creating the first table, maybe it can be improved to avoid fixing it.


That output looks a lot like Proc Freq ods output onewayfreqs for multiple variables with some variables removed.

Lefty
Obsidian | Level 7

Thanks, yes, this is true, maybe I should have started my question there. Here's the original code I did:

 

ods output crosstabfreqs=freqs;

proc freq data=mydata;

format sex sexf. region regionf.;

tables (sex age region)*stratvar/norow nopercent;run;

 

And then my ultimate goal is to get the table I listed in my original post. If there's a better ods table to use, I'd love to know about it.
Thank you again!

Reeza
Super User

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sashelp.class;
	table sex age;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc print data=want(obs=20) label;
run;
Lefty
Obsidian | Level 7
Thanks Reza! Is there a way to do this if I want crosstabs, so tables (sex age) * race; for example?
Reeza
Super User

ods output crosstabfreqs=summary;
proc freq data=sashelp.class;
table sex*(_all_);
run;



data long;
	length variable $32. variable_value $50.;
	set summary;
	Variable=scan(table, 2, '*');
	Variable_Value=strip(trim(vvaluex(variable)));
	presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
	keep sex variable  variable_value frequency percent presentation;
	label variable='Variable' variable_value='Variable Value';
run;

 https://gist.github.com/statgeek/0c4aeec9053cf8050be18a03b842c1b9

Lefty
Obsidian | Level 7
This is even better than I hoped, thank you so much!

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
  • 12 replies
  • 1596 views
  • 4 likes
  • 7 in conversation