Select when statements

[edhuang]

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;

[ChrisNZ]

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

[edhuang]

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;

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;

run;

[Kurt_Bremser · Accepted solution]

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;

[Astounding]

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;

[PGStats]

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;

[ballardw]

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)