Compiling non-missing data across columns into one column

[Lefty]

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!

[Tom]

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]

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]

Please show us the exact code you used.

[Tom]

---

@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]

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]

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

[ballardw]

---

@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]

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]

*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]

Thanks Reza! Is there a way to do this if I want crosstabs, so tables (sex age) * race; for example?

[Reeza · Accepted solution]

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]

This is even better than I hoped, thank you so much!