topic Re: How to find column name which has a value in it in SAS Programming

How to find column name which has a value in it

akhilesh_joshi — Thu, 01 Dec 2016 14:04:33 GMT

Hi All,

My data looks like this.

x_1 x_2 x_3 x_4 x_5 x_6 x_7 x_8 x_9 x_10 x_11 x_12 x_13 x_14 x_15 x_16
2
3

4
2
3
5
7
8

I want to get column name when there exists a value in it. For example, x_14 in first run, X_12 in next and so on. I do not want to get column name when value exists in x_15 or x_16.

Thanks for help in advance!

Re: How to find column name which has a value in it

Reeza — Thu, 01 Dec 2016 14:08:14 GMT

Assuming only one column is ever populated.

Array nums(*) x_1-x_14;

max=max(of nums(*));

index=whichn(max, of nums(*));

variableName = vname(nums(index));

You could also use the index variable to create the variable name since your variables are indexed from 1 to 14.

Re: How to find column name which has a value in it

slchen — Thu, 01 Dec 2016 15:27:07 GMT

Try to induce a dummy variable, update to collapse dataset, then find variables without missing values.

data have;

set have;

dummy=1;

run;

data want(keep=Var_nomissing);

update have(obs=0) have end=last;
by dummy;
array var x:;
if last then do;
do over var;
if not missing(var) then Var_nomissing=vname(var);
output;
end;
end;
run;

Re: How to find column name which has a value in it

RW9 — Thu, 01 Dec 2016 15:45:32 GMT

Why do you have so many variables called x_I in the first place as there only appears to be 1 data item per block of variables? Seems to be a waste of space, fix the problem in the previous steps as:

X NUM

15 2

13 3

...

Would be simpler and easier to use (if you need X at all.

Re: How to find column name which has a value in it

ballardw — Thu, 01 Dec 2016 16:03:45 GMT

@RW9 , let's see if the OP responds on the variables. However I have worked with at least two separate survey data collection programs that would export the data of single choice responses in that manner by default. So if your question had 15 possible responses you received a data set with Q1_1 Q1_2 ... Q1_15 for the 15 responses to Question 1. You really don't want to see the garbage created by multiple choice responses.

Re: How to find column name which has a value in it

akhilesh_joshi — Thu, 01 Dec 2016 16:12:47 GMT

Hi,

I need those variables in those format. All of them represent something unique and cnanot be converted to the format you mentioned. Ballardw is right.

Re: How to find column name which has a value in it

akhilesh_joshi — Thu, 01 Dec 2016 17:10:53 GMT

Hi Reeza,

When I don't get a value in x_1 to x_14 (i.e value exists in x_15 or x_16), it gives me an array subscript out of range error. Also, if I change your code to include x_1 to x_16, it always gets the max value in x_16 as x_16 always has values. x_16 will never be blank. Please advice. Sorry for not being precise earlier.

x_1 x_2 x_3 x_4 x_5 x_6 x_7 x_8 x_9 x_10 x_11 x_12 x_13 x_14 x_15 x_16
2 9
3 10

4 11
2 12
3 13
5 14
7 16
8

Re: How to find column name which has a value in it

RW9 — Thu, 01 Dec 2016 17:28:44 GMT

True, we are never going to sort out all data capture devices out there, to get rid of the "have to" use Excel, transposed data mentality. However that doesn't mean we have to work with it that way. There are some pushes towards this, QS domain SDTM for instance, question name and answer, only capture the actual information. First task in any programming would be data modelling, however this step seems to be skipped in most cases.

Re: How to find column name which has a value in it

Reeza — Fri, 02 Dec 2016 00:40:44 GMT

If you don't want x_16 why are you including it in the array declaration?

Add an IF Condition, such that if there is no value then it doesn't try and get the variable name.

If index ne 0 then variable_name = ...;