topic Re: Compiling non-missing data across columns into one column in SAS Programming

Compiling non-missing data across columns into one column

Lefty — Wed, 27 Sep 2023 22:46:50 GMT

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!

Re: Compiling non-missing data across columns into one column

Tom — Wed, 27 Sep 2023 23:05:10 GMT

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;

Re: Compiling non-missing data across columns into one column

andreas_lds — Thu, 28 Sep 2023 06:36:11 GMT

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

Re: Compiling non-missing data across columns into one column

ballardw — Thu, 28 Sep 2023 07:27:11 GMT

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

Re: Compiling non-missing data across columns into one column

Lefty — Thu, 28 Sep 2023 17:34:31 GMT

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?

Re: Compiling non-missing data across columns into one column

PaigeMiller — Thu, 28 Sep 2023 17:39:49 GMT

Please show us the exact code you used.

Re: Compiling non-missing data across columns into one column

Lefty — Thu, 28 Sep 2023 17:39:58 GMT

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!

Re: Compiling non-missing data across columns into one column

Tom — Thu, 28 Sep 2023 17:40:46 GMT

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

Re: Compiling non-missing data across columns into one column

Reeza — Thu, 28 Sep 2023 17:41:11 GMT


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

Re: Compiling non-missing data across columns into one column

Lefty — Thu, 28 Sep 2023 17:47:05 GMT

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

Re: Compiling non-missing data across columns into one column

Kurt_Bremser — Thu, 28 Sep 2023 17:53:48 GMT

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.

Re: Compiling non-missing data across columns into one column

Reeza — Thu, 28 Sep 2023 17:56:51 GMT


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

Re: Compiling non-missing data across columns into one column

Lefty — Thu, 28 Sep 2023 17:58:35 GMT

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