BookmarkSubscribeRSS Feed
akhilesh_joshi
Fluorite | Level 6

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!

8 REPLIES 8
Reeza
Super User

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. 

akhilesh_joshi
Fluorite | Level 6

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  

Reeza
Super User

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

 

 

slchen
Lapis Lazuli | Level 10

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

akhilesh_joshi
Fluorite | Level 6

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.

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
  • 8 replies
  • 4204 views
  • 0 likes
  • 5 in conversation