How to find column name which has a value in it

Reply
Contributor
Posts: 40

How to find column name which has a value in it

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!

Super User
Posts: 19,165

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

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. 

Contributor
Posts: 40

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

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  

Super User
Posts: 19,165

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

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 = ...;

 

 

Super Contributor
Posts: 275

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

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;

Super User
Super User
Posts: 7,720

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

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.

Super User
Posts: 11,134

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

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

Super User
Super User
Posts: 7,720

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

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.

Contributor
Posts: 40

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

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.

Ask a Question
Discussion stats
  • 8 replies
  • 378 views
  • 0 likes
  • 5 in conversation