DATA Step, Macro, Functions and more

Using an array on multiple variables for output to one variable (9.4)

Accepted Solution Solved
Reply
New Contributor aeb
New Contributor
Posts: 3
Accepted Solution

Using an array on multiple variables for output to one variable (9.4)

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!


Accepted Solutions
Solution
‎11-16-2017 04:19 PM
Super User
Posts: 23,293

Re: Using an array on multiple variables for output to one variable (9.4)

[ Edited ]

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


All Replies
Solution
‎11-16-2017 04:19 PM
Super User
Posts: 23,293

Re: Using an array on multiple variables for output to one variable (9.4)

[ Edited ]

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!


 

Super User
Posts: 13,321

Re: Using an array on multiple variables for output to one variable (9.4)

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.

New Contributor aeb
New Contributor
Posts: 3

Re: Using an array on multiple variables for output to one variable (9.4)

This was correct as well. Thank you!

Super User
Posts: 6,632

Re: Using an array on multiple variables for output to one variable (9.4)

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. 

New Contributor aeb
New Contributor
Posts: 3

Re: Using an array on multiple variables for output to one variable (9.4)

Posted in reply to Astounding

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
Super User
Posts: 23,293

Re: Using an array on multiple variables for output to one variable (9.4)

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

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

Super User
Posts: 13,321

Re: Using an array on multiple variables for output to one variable (9.4)

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 184 views
  • 1 like
  • 4 in conversation