topic Re: Select when statements in SAS Programming

Select when statements

edhuang — Mon, 07 Dec 2020 06:00:51 GMT

Hi,

I have a character variable scope_number_or and I like to categorize into a numeric variable HDscope.

I like categorize scope_number_or by looking at substring of characters (like ones that start with 24, 25, 26 and ends with 27) and I tried using 'like' operator in my select-when statement, but 'like' operator does not seem to work. Is there a way to do this in a select-when statement?

select;
when (scope_number_or like "24%",
scope_number_or like "25%",
scope_number_or like "26%",
scope_number_or like "%27") HDscope=0;
when (scope_number_or like "N/A") HDscope=.;
otherwise HDscope=1;
end;

Re: Select when statements

ChrisNZ — Mon, 07 Dec 2020 06:32:43 GMT

The like operator works fine.

proc sql; select NAME, case when NAME like 'A%' then 1 else 2 end 'N' from SASHELP.CLASS(obs=9);

Name	N
Joyce	2
Thomas	2
James	2
Jane	2
John	2
Louise	2
Robert	2
Alice	1
Barbara	2

Re: Select when statements

edhuang — Mon, 07 Dec 2020 14:53:25 GMT

Hi Chris,

Thanks for your response.

However, I am doing this in a data step and not under proc sql.

I have several other select-when statements in my data step, so I like to be able to do it in my data step.

Is there a way to do this?

Example:

Data two;

set one;

run;

Re: Select when statements

Kurt_Bremser — Mon, 07 Dec 2020 15:15:58 GMT

select;
  when (substr(scope_number_or,1,2) in ("24","25","26") or substr(scope_number_or,length(scope_number_or)-1) = "27") HDscope = 0;
  when (scope_number_or = "N/A") HDscope = .;
  otherwise HDscope = 1;
end;

Re: Select when statements

Astounding — Mon, 07 Dec 2020 15:22:35 GMT

In a DATA step, SELECT would not be needed. For example, this comparison would work:

if scope_number_or in: ('24', '25', '26') and 
substr(scope_number_or, length(scope_number_or)-1) = '27'
then HDscope=0;

Re: Select when statements

PGStats — Mon, 07 Dec 2020 15:46:55 GMT

You cannot list logical expressions in the WHEN clause, you have to use separate clauses:

Data two;
set one;
select;
when (scope_number_or like "24%") HDscope=0;
when (scope_number_or like "25%") HDscope=0;
when (scope_number_or like "26%") HDscope=0;
when (scope_number_or like "%27") HDscope=0;
when (scope_number_or like "N/A") HDscope=.;
otherwise HDscope=1;
end;
run;

Re: Select when statements

ballardw — Mon, 07 Dec 2020 16:03:28 GMT

How many actual values are you dealing with? 10, 100 or 1000 ?

This might be easier with a custom informat if the list is relatively static.

And why "end with 27"? That sounds like an odd coding scheme when the others you are concerned with start with 24, 25 and 26 (where 27 would appear to be in SEQUENCE)