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!
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.
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
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 = ...;
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;
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.
@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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.