Hi all,
I have 21 variables of results (rslt_1 - rslt_21). Values within each variable are 'yes', 'no', 'unknown' and blanks. I am interested in one variable, lets call it helpme, that will tell me whether or not a subject ever had a 'yes' in any of the 21 results. If not, then I want the value of helpme to be either no or none if results were no, unknown, or blank. For example, I would like my final data to look like this:
rslt_1 rslt_2 rslt_3 ... rslt_21 helpme
yes no unknown yes
no no no
yes yes yes
none
I use an array, right? But I'm getting stuck on creating the new variable. Here's the code I have tried, which creates "helpme" but without any values and SAS tries to convert my character variables to numeric.
data want (drop=i);
set have;
array rsltarray (21) rslt_1-rslt_21;
do i=1 to 21;
if rsltarray(i) in ('yes') then helpme='yes';
else if rsltarray(i) in ('no', 'unknown') then helpme='no';
else if rsltarray(i) in ('') then helpme='none';
end;
run;
Thanks so much!
Look up the WHICHC function instead.
@aeb wrote:
Hi all,
I have 21 variables of results (rslt_1 - rslt_21). Values within each variable are 'yes', 'no', 'unknown' and blanks. I am interested in one variable, lets call it helpme, that will tell me whether or not a subject ever had a 'yes' in any of the 21 results. If not, then I want the value of helpme to be either no or none if results were no, unknown, or blank. For example, I would like my final data to look like this:
rslt_1 rslt_2 rslt_3 ... rslt_21 helpme
yes no unknown yes
no no no
yes yes yes
none
I use an array, right? But I'm getting stuck on creating the new variable. Here's the code I have tried, which creates "helpme" but without any values and SAS tries to convert my character variables to numeric.
data want (drop=i);
set have;
array rsltarray (21) rslt_1-rslt_21;
do i=1 to 21;
if rsltarray(i) in ('yes') then helpme='yes';
else if rsltarray(i) in ('no', 'unknown') then helpme='no';
else if rsltarray(i) in ('') then helpme='none';
end;
run;
Thanks so much!
Look up the WHICHC function instead.
@aeb wrote:
Hi all,
I have 21 variables of results (rslt_1 - rslt_21). Values within each variable are 'yes', 'no', 'unknown' and blanks. I am interested in one variable, lets call it helpme, that will tell me whether or not a subject ever had a 'yes' in any of the 21 results. If not, then I want the value of helpme to be either no or none if results were no, unknown, or blank. For example, I would like my final data to look like this:
rslt_1 rslt_2 rslt_3 ... rslt_21 helpme
yes no unknown yes
no no no
yes yes yes
none
I use an array, right? But I'm getting stuck on creating the new variable. Here's the code I have tried, which creates "helpme" but without any values and SAS tries to convert my character variables to numeric.
data want (drop=i);
set have;
array rsltarray (21) rslt_1-rslt_21;
do i=1 to 21;
if rsltarray(i) in ('yes') then helpme='yes';
else if rsltarray(i) in ('no', 'unknown') then helpme='no';
else if rsltarray(i) in ('') then helpme='none';
end;
run;
Thanks so much!
If your variables were more sensible coded as 1,0 and a special missing for Unknown this would be pretty trivial as you could use MAX and Min functions.
Your code as written will always have the value for the last item in the array.
There are other functions than equality available:
data want (drop=i); set have; array rsltarray (21) rslt_1-rslt_21; if whichc('yes',of rsltarray(*)) > 0 then helpme='yes'; else if cmiss(of rsltarray(*) = 21 then helpme='none'; else helpme='no'; run;
whichc returns the number of the first word in the list (here the array) that the first string value appears, or 0
CMISS counts the missing string values, if 21 then all are missing
otherwise the has to be at least one "no" if your data description is correct.
This was correct as well. Thank you!
The first thing to resolve is what is actually in your data. When you say that SAS is converting your variables to numeric, the most likely situation is that they are already numeric. They just appear to be character, because there is a format attached to them. Run a PROC CONTENTS on your data set to see what is actually in there. (There are other possibilities, such as the variable names actually having a slightly different spelling.) Also run 2 PROC FREQs to see what the values are.
proc freq data=have;
tables rslt_1-rslt_21;
run;
proc freq data=have;
tables rslt_1-rslt_21;
format rslt_1-rslt_21;
run;
Once we know what is in there, the programming can begin.
Variables are definitely character (I renamed my variables in the posting for simplicity). Attempting the WHICHC function suggested, but it is overlooking the "yes" responses. Only getting "no" and "none" in my final output. I inherited a rough data set, thanks for your patience!
SAS Output
asprslt_1 | Char | 7 | $7. | $7. | asprslt_1 |
asprslt_2 | Char | 3 | $3. | $3. | asprslt_2 |
asprslt_3 | Char | 3 | $3. | $3. | asprslt_3 |
It's case sensitive. Is it 'yes' or 'Yes'?
And if that doesn't work, check for trailing spaces or such.
@aeb wrote:
Variables are definitely character (I renamed my variables in the posting for simplicity). Attempting the WHICHC function suggested, but it is overlooking the "yes" responses. Only getting "no" and "none" in my final output. I inherited a rough data set, thanks for your patience!
No data=> no tested code
No posted code / log => we have no idea what you actually ran.
It appears most likely that you may have missed the first "else" in the code that I suggested.
BTW your example description of 3 variables indicates that ONLY asprslt_1 of the three shown could have the value of "unknown" due to the length of the variable.
Which indicates someone relied on Proc Import at some point in the past to read the data instead of forcing all of the asprslt variables to use the same informat/ length which should be the case if they are using the same measurement scale.
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.