Hey-
I need to use a 'case when' to categorize the right billcodes, for example, code 'A100', 'A101', 'A102', ..., 'A199' need to be put under 'Category 1'.
however I want to find a short way using
case when billcode in ('A100', 'A101', 'A102', ..., 'A199' ) then 'Category 1'
I dont want to list 100 codes separately in the above parenthesis, do you know if there is a good/short way of doing that?
Greatly appreciate!
Like this perhaps?
case when billcode =: 'A1' then 'Category 1'
Assuming all values starting with A1 should be selected.
case when billcode in: ('A10', 'A11','A12','A13','A14','A151','A152','A153','A154','A155','A156','A157','A158') then 'Category 1'
thank you. I am using SAS 9, it looks like my SAS cannot recognize the ' : ' in the code, it give error msg
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.
Do you know any other way of doing this?
Thank you!
I wouldn't call below the best approach but it will create a macro variable which will list the CASE Expression values:
%macro list_codes;
%macro _;
%mend _;
%global var;
data a;
do i = 100 to 199;
var = cats("'","A",i,"'");
output;
end;
run;
proc sql noprint;
select var
into :var separated by ","
from a;
quit;
proc datasets lib=work nodetails nolist;
delete a;
run;
quit;
%mend;
/* Test */
data s;
var = 'A100';
run;
proc sql;
create table want as
select *,
case
when var in (&var.) then 'Category 1'
else 'Category 2'
end
as cat
from s;
quit;
Please post the complete SAS log of your SQL program.
This partial solution should work:
case when substr(billcode,1,3) in ('A10', 'A11','A12','A13','A14') then 'Category 1'
@LightJade wrote:
thank you. but what if I have
case when billcode in ('A100', 'A101', 'A102', ..., 'A158' ) then 'Category 1'
thank you!
I would solve such issues with a custom format:
data cntlin_ds;
fmtname = "billcode";
type = "C";
label = "category 1";
do i = 1 to 158;
start = "A" !! put(i,z3.);
output;
end;
run;
proc format cntlin=cntlin_ds;
run;
proc sql;
create table want as
select
put(billcode,$billcode.) as newvar
from have
;
quit;
Expand the first data step to cover other ranges, and possibly add one observation for "other" with hlo = "O".
Hi @LightJade,
It looks like you're using SQL. The below code creates A1, A2,..., A200 and then applies the rule you want (as I understand it).
If you need a different outcome then please provide the output you need based on some given input.
/* set up data A1, A2, ..., A199, A200 */
data have;
do i = 1 to 200;
billcode = cats('A',i);
output;
end;
run;
/* ignore 'A' and then test number */
proc sql noprint;
create table
want
as select
billcode
,case
when input(substr(billcode,2),8.) between 100 and 158
then 'Category 1'
else 'Category 0'
end as category
from
have
;
quit;
Kind regards,
Amir.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.