BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aeb
Calcite | Level 5 aeb
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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!


 

View solution in original post

7 REPLIES 7
Reeza
Super User

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!


 

ballardw
Super User

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.

aeb
Calcite | Level 5 aeb
Calcite | Level 5

This was correct as well. Thank you!

Astounding
PROC Star

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. 

aeb
Calcite | Level 5 aeb
Calcite | Level 5

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_1Char7$7.$7.asprslt_1
asprslt_2Char3$3.$3.asprslt_2
asprslt_3Char3$3.$3.asprslt_3
Reeza
Super User

It's case sensitive. Is it 'yes' or 'Yes'?

And if that doesn't work, check for trailing spaces or such. 

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 5410 views
  • 1 like
  • 4 in conversation