topic Can someone please help me find the right way to set up the criteria in 'Case when' ? in SAS Programming

Can someone please help me find the right way to set up the criteria in 'Case when' ?

LightJade — Tue, 23 Feb 2021 01:56:31 GMT

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!

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

SASKiwi — Tue, 23 Feb 2021 02:03:02 GMT

Like this perhaps?

case when billcode =: 'A1' then 'Category 1'

Assuming all values starting with A1 should be selected.

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

LightJade — Tue, 23 Feb 2021 02:06:14 GMT

thank you. but what if I have

case when billcode in ('A100', 'A101', 'A102', ..., 'A158' ) then 'Category 1'

thank you!

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

SASKiwi — Tue, 23 Feb 2021 02:14:27 GMT

case when billcode in: ('A10', 'A11','A12','A13','A14','A151','A152','A153','A154','A155','A156','A157','A158') then 'Category 1'

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

LightJade — Tue, 23 Feb 2021 02:29:05 GMT

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!

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

qoit — Tue, 23 Feb 2021 02:39:27 GMT

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;

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

SASKiwi — Tue, 23 Feb 2021 03:02:23 GMT

Please post the complete SAS log of your SQL program.

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

SASKiwi — Tue, 23 Feb 2021 03:20:11 GMT

This partial solution should work:

case when substr(billcode,1,3) in ('A10', 'A11','A12','A13','A14') then 'Category 1'

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

Amir — Tue, 23 Feb 2021 11:13:57 GMT

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.

Re: Can someone please help me find the right way to set up the criteria in 'Case when' ?

Kurt_Bremser — Tue, 23 Feb 2021 11:50:35 GMT

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