topic Re: Multiple If/Then/Else statements with wildcard in SAS Programming

Multiple If/Then/Else statements with wildcard

lturian — Fri, 10 Jul 2020 21:30:30 GMT

I'm trying to create a new column by grouping variables from a different column to a new output. I'm also trying to identify those variables just by their first 2 digits using a wildcard. When I run the following, the output just puts "1" in the new column for all rows, even if doesn't abide by the intended "if" statement. I'm not quite sure why it does that and fails to have the intended output in the new column.

Any help would be greatly appreciated!!

data test; 
	set test; 
	if HOSPSTCO=:'23' or '33' or '50' or '25' or '44' or '09' then HOSP_DIVISION=1; 
	else if HOSPSTCO=:'36' or '42' or '34' then HOSP_DIVISION=2; 
	else if HOSPSTCO=:'55' or '26' or '17' or '18' or '39' then HOSP_DIVISION=3; 
	else if HOSPSTCO=:'29' or '38' or '46' or '31' or '20' or '27' or '19' then HOSP_DIVISION=4; 
	else if HOSPSTCO=:'10' or '24' or '11' or '51' or '54' or '37' or '45' or '13' or '12' then HOSP_DIVISION=5; 
	else if HOSPSTCO=:'21' or '47' or '28' or '01' then HOSP_DIVISION=6; 
	else if HOSPSTCO=:'40' or '48' or '05' or '22' then HOSP_DIVISION=7; 
	else if HOSPSTCO=:'16' or '30' or '56' or '32' or '49' or '08' or '04' or '35' then HOSP_DIVISION=8; 
	else if HOSPSTCO=:'02' or '53' or '41' or '06' or '15' then HOSP_DIVISION=9; 
run;

Re: Multiple If/Then/Else statements with wildcard

PaigeMiller — Fri, 10 Jul 2020 21:40:33 GMT

The proper syntax is:

if HOSPSTCO=:'23' or HOSPSTCO=:'33' or HOSPSTCO=:'50' or 
    HOSPSTCO=:'25' or HOSPSTCO=:'44' or HOSPSTCO=:'09' 
    then HOSP_DIVISION=1;

Alternative

if substr(hospstco,1,2) in ('23','33','50','25','44','09') then hosp_division=1;

Re: Multiple If/Then/Else statements with wildcard

ballardw — Fri, 10 Jul 2020 21:47:31 GMT

The proper operator to use is IN .

if substr(HOSPSTCO,1,2) in ('23' '33' '50' '25' '44' '09') then HOSP_DIVISION=1;

Or use the SELECT When for doing lots of comparisons instead of If/then/else

data test2; 
	set test; 
   select (substr(HOSPSTCO,1,2));
   	When ('23' , '33' , '50' , '25' , '44' , '09' ) HOSP_DIVISION=1; 
   	When ('36' , '42' , '34' ) HOSP_DIVISION=2; 
   	When ('55' , '26' , '17' , '18' , '39' ) HOSP_DIVISION=3; 
   	When ('29' , '38' , '46' , '31' , '20' , '27' , '19' ) HOSP_DIVISION=4; 
   	When ('10' , '24' , '11' , '51' , '54' , '37' , '45' , '13' , '12' ) HOSP_DIVISION=5; 
   	When ('21' , '47' , '28' , '01' ) HOSP_DIVISION=6; 
   	When ('40' , '48' , '05' , '22' ) HOSP_DIVISION=7; 
   	When ('16' , '30' , '56' , '32' , '49' , '08' , '04' , '35' ) HOSP_DIVISION=8; 
   	When ('02' , '53' , '41' , '06' , '15' ) HOSP_DIVISION=9; 
      otherwise;
   end;
run;

The otherwise is not required but there will be a warning if not present. It is to do what ever you might want if none of the values appear in the other When conditions. Such as perhaps put a warning to the log about an unexpected value or missing value.

Another alternative if you have a data set with the code values and categories would be to create a format to assign the division code and just use the format instead of adding another variable.

This takes advantage of an oddity in range comparisons of character values:

proc format;
value $hospdiv
'23' - '2399'= 'Division 1'
'33' - '3399'= 'Division 1'
'36' - '3699'= 'Division 2'
'42' - '4299'= 'Division 2'
;
run;

data junk;
  input x $;
  put x= $hospdiv.;
datalines;
23
231
2345
33
3356
36
367
3678
42
4244
;

I have several formats that are used to turn location codes into regions, nested region/subregion (multilabel formats), long and short descriptions.

Groups created with formats will be honored by most analysis, reporting and graphing procedures. They have a big advantage that if you need to make a small "tweek" you only need to modify Format code and do not have to go back to the data set to add yet another variable.

Re: Multiple If/Then/Else statements with wildcard

Reeza — Fri, 10 Jul 2020 21:49:50 GMT

Formats are your friend.

Untested but something like this is a lot cleaner and easier to maintain and test. My nickel at least.

proc format;
invalue  hosp_division
'23'  '33'  '50' '25'  '44' '09'  = 1
'36'  '42'  '34' = 2
'55'  '26'  '17'  '18'  '39' =3;
run;

data test2;*use a different name otherwise it's hard to track changes over time and harder to debug;
set test; 

hosp_division = input(substr(hospstco, 1, 2), hosp_division.);
run;

@lturian wrote:

I'm trying to create a new column by grouping variables from a different column to a new output. I'm also trying to identify those variables just by their first 2 digits using a wildcard. When I run the following, the output just puts "1" in the new column for all rows, even if doesn't abide by the intended "if" statement. I'm not quite sure why it does that and fails to have the intended output in the new column.

Any help would be greatly appreciated!!
data test; 
	set test; 
	if HOSPSTCO=:'23' or '33' or '50' or '25' or '44' or '09' then HOSP_DIVISION=1; 
	else if HOSPSTCO=:'36' or '42' or '34' then HOSP_DIVISION=2; 
	else if HOSPSTCO=:'55' or '26' or '17' or '18' or '39' then HOSP_DIVISION=3; 
	else if HOSPSTCO=:'29' or '38' or '46' or '31' or '20' or '27' or '19' then HOSP_DIVISION=4; 
	else if HOSPSTCO=:'10' or '24' or '11' or '51' or '54' or '37' or '45' or '13' or '12' then HOSP_DIVISION=5; 
	else if HOSPSTCO=:'21' or '47' or '28' or '01' then HOSP_DIVISION=6; 
	else if HOSPSTCO=:'40' or '48' or '05' or '22' then HOSP_DIVISION=7; 
	else if HOSPSTCO=:'16' or '30' or '56' or '32' or '49' or '08' or '04' or '35' then HOSP_DIVISION=8; 
	else if HOSPSTCO=:'02' or '53' or '41' or '06' or '15' then HOSP_DIVISION=9; 
run; 

Re: Multiple If/Then/Else statements with wildcard

PaigeMiller — Fri, 10 Jul 2020 22:02:11 GMT

All good replies.

And if in the real-world example there are cases where you sometimes want the first two characters of HOSPSTCO and other cases where you want the first three (or four or ... ) characters to match, then I think all of the solutions fail except the IF/THEN construct. But maybe I'm wrong, maybe there's a way to get the SUBSTR or FORMAT solution to work there, I just can think of it right now.

Re: Multiple If/Then/Else statements with wildcard

Reeza — Fri, 10 Jul 2020 22:20:39 GMT

I think this code is the hospital modified FIPS State/County code, which is why this solution should work overall. But it definitely may not generalize to all IF/THEN questions.

https://www.hcup-us.ahrq.gov/db/vars/hospstco/kidnote.jsp

Re: Multiple If/Then/Else statements with wildcard

Astounding — Sat, 11 Jul 2020 02:22:44 GMT

@Reeza

Just curious whether you tried to replace this:

hosp_division = input(substr(hospstco, 1, 2), hosp_division.);

Is it possible this would work?

hosp_division = input(hospstco, hosp_division2.);

Re: Multiple If/Then/Else statements with wildcard

Tom — Sat, 11 Jul 2020 03:44:11 GMT

@Astounding wrote:

@Reeza

Just curious whether you tried to replace this:
hosp_division = input(substr(hospstco, 1, 2), hosp_division.);
Is it possible this would work?
hosp_division = input(hospstco, hosp_division2.);

That will work.

Re: Multiple If/Then/Else statements with wildcard

Tom — Sat, 11 Jul 2020 03:50:09 GMT

What do you think the result should be to your boolean expressions that are comparing two strings with the OR operator? Let's test it and see what SAS does.

567   data _null_;
568     if '01' or '02' then put 'yes';
569     else put 'no';
570   run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      568:6    568:14
yes

So it looks like it will convert those strings to numbers and do the OR. SAS will treat any value other than zero or missing as TRUE so your first IF test is going to always be true since all of the strings look like non-zero numbers.

If you want to compare one variable to any of a series of value using the IN operator. You can use the colon modifier on the IN operator just like you used it on the equals operator.

if HOSPSTCO in: ('23'  '33'  '50'  '25'  '44'  '09') then ...

Re: Multiple If/Then/Else statements with wildcard

lturian — Sat, 11 Jul 2020 04:14:43 GMT

When I try this, it doesn't output any variable into HOSP_DIVISION. Every row just has "." instead of the desired output. I wonder if the problem lies with the HOSPSTCO data

Re: Multiple If/Then/Else statements with wildcard

lturian — Sat, 11 Jul 2020 04:16:16 GMT

I tried both ways and for some reason I'm not getting anything in the new column. I'm just getting "." in all rows.

Re: Multiple If/Then/Else statements with wildcard

lturian — Sat, 11 Jul 2020 04:57:12 GMT

I do like formats. But for some reason I'm not getting anything the formatted variables in the new column. I'm just getting "." in all rows.

Re: Multiple If/Then/Else statements with wildcard

ballardw — Sat, 11 Jul 2020 10:41:18 GMT

@lturian wrote:
I do like formats. But for some reason I'm not getting anything the formatted variables in the new column. I'm just getting "." in all rows.

Values? Format / informat definition? Code used?

Re: Multiple If/Then/Else statements with wildcard

Kurt_Bremser — Sat, 11 Jul 2020 10:59:32 GMT

Why is this condition:

if HOSPSTCO=:'23' or '33' or '50' or '25' or '44' or '09' then HOSP_DIVISION=1;

always true?

Well, in a condition, the comparisons take precedence, then the NOT operator, then AND, then OR.

Therefore your condition translates to

if (HOSPSTCO=:'23') or ('33') or ('50') or ('25') or ('44') or ('09') then HOSP_DIVISION=1;

Since a boolean value needs to be numeric, the character values are converted on-the-fly, and since they all convert to non-zero and non-missing numbers, they are considered true, so this condition as a whole will always be true.

You want to use the IN operator and a set of values.

Re: Multiple If/Then/Else statements with wildcard

PaigeMiller — Sat, 11 Jul 2020 11:22:39 GMT

@lturian wrote:
When I try this, it doesn't output any variable into HOSP_DIVISION. Every row just has "." instead of the desired output. I wonder if the problem lies with the HOSPSTCO data

Then LOOK AT the data in data set TEST with your own eyes.

Re: Multiple If/Then/Else statements with wildcard

ChrisNZ — Sat, 11 Jul 2020 11:32:37 GMT

if HOSPSTCO=:'23' or '33' or '50' or '25' or '44' or '09' then HOSP_DIVISION=1;

This must generate quite a few note sin the log about type conversion.

These notes should never be tolerated: They indicate that the compiler finds data inconsistencies in the way a program is written

Remove the notes and the new program will work as expected.

Hint: HOSPSTCO=:'23' is a Boolean number '33' is also a Boolean number

Re: Multiple If/Then/Else statements with wildcard

lturian — Sat, 11 Jul 2020 15:58:53 GMT

I used the invalue format you provided:

proc format;
	invalue FIPS
	'23' '33' '50' '25' '44' '09' = 1
	'36' '42' '34' = 2
	'55' '26' '17' '18' '39' = 3
	'29' '38' '46' '31' '20' '27' '19' = 4
	'10' '24' '11' '51' '54' '37' '45' '13' '12' = 5
	'21' '47' '28' '01' = 6
	'40' '48' '05' '22' = 7
	'16' '30' '56' '32' '49' '08' '04' '35' = 8
	'02' '53' '41' '06' '15' = 9
	;
run;

data test2;
	set test;
HOSP_DIVISION = input(substr(HOSPSTCO,1,2), FIPS.);
run;

Re: Multiple If/Then/Else statements with wildcard

Tom — Sat, 11 Jul 2020 22:04:44 GMT

@lturian wrote:

I used the invalue format you provided:

proc format;
	invalue FIPS
	'23' '33' '50' '25' '44' '09' = 1
	'36' '42' '34' = 2
	'55' '26' '17' '18' '39' = 3
	'29' '38' '46' '31' '20' '27' '19' = 4
	'10' '24' '11' '51' '54' '37' '45' '13' '12' = 5
	'21' '47' '28' '01' = 6
	'40' '48' '05' '22' = 7
	'16' '30' '56' '32' '49' '08' '04' '35' = 8
	'02' '53' '41' '06' '15' = 9
	;
run;

data test2;
	set test;
HOSP_DIVISION = input(substr(HOSPSTCO,1,2), FIPS.);
run;

And the result was good? bad? ugly? What?

Are you sure the variable HOSPSTCO is defined as character?

Re: Multiple If/Then/Else statements with wildcard

Reeza — Sun, 12 Jul 2020 23:58:17 GMT

This seems to work for me.

data test;
input HOSPSTCO $ ;
cards;
23000
33000
02124
34002
55343
;
run;


proc format;
	invalue $  FIPS
	'23', '33', '50', '25', '44', '09'                    = 1
	'36', '42', '34'                                      = 2
	'55', '26', '17', '18', '39'                          = 3
	'29', '38', '46', '31', '20', '27', '19'              = 4
	'10', '24', '11', '51', '54', '37', '45', '13', '12'  = 5
	'21', '47', '28', '01'                                = 6
	'40', '48', '05', '22'                                = 7
	'16', '30', '56', '32', '49', '08', '04', '35'        = 8
	'02', '53', '41', '06', '15'                          = 9
	;
run;

data test2;
	set test;
    HOSP_DIVISION = input(substr(HOSPSTCO, 1, 2) , $FIPS.);
    HOSP_DIVISION2 = input(HOSPSTCO, $FIPS2.);
run;