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

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

aeb — Thu, 16 Nov 2017 20:51:23 GMT

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!

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

Reeza — Thu, 16 Nov 2017 21:00:25 GMT

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!

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

ballardw — Thu, 16 Nov 2017 21:04:16 GMT

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.

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

Astounding — Thu, 16 Nov 2017 21:08:12 GMT

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.

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

aeb — Thu, 16 Nov 2017 21:14:50 GMT

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

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

Reeza — Thu, 16 Nov 2017 21:16:27 GMT

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

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

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

ballardw — Thu, 16 Nov 2017 21:21:39 GMT

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

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

aeb — Thu, 16 Nov 2017 21:20:20 GMT

This was correct as well. Thank you!