topic Re: if then statement to create new variables of ranges in SAS Procedures

if then statement to create new variables of ranges

starz4ever2007 — Thu, 09 Feb 2017 05:33:46 GMT

Hi I tried to do this in Excel, but was unsuccessful. Now I need help trying to do this in SAS, please :):

Data looks like this (in months---not important)....and Age1 is the beginning of an age range and Age2 is the end.

Age1 Age2

13 60 <---(data collected like this, but basically means that age range is 13-60)

50 70

168 780

0 0

0 240

all all

192 older

I want to create new variables that are interval groups to see where each age range falls:

Interval groups are:

<12 is group1

12-60 is group2

61-132 is group3

133 - 204 is group4

205 < is group5

.....and if "all" in both Age1 and Age2 then they go in all groups

.....and if "0" in both Age1 and Age2 then they go in group1 only

.....and if "older" in Age2 ("older won't be found in Age1), then it goes into the group of where Age1 would fall and all groups after that

.....see that depending on the range (between Age1 and Age2), counts can be in multiple groups (see below)

Data should look like:

Age1 Age2 group1 group2 group3 group4 group5

13 60 0 1 0 0 0 <-(only falls in group2)

50 70 0 1 1 0 0 <-(both group2&group3)

168 780 0 0 0 1 1

0 0 1 0 0 0 0

0 240 1 1 1 1 1

all all 1 1 1 1 1 <-(all groups)

62 older 0 0 1 1 1 <-(group3&each one after)

Any help would be appreciated! Thanks in advance!

Re: if then statement to create new variables of ranges

RW9 — Thu, 09 Feb 2017 09:02:18 GMT

Well, as you haven't provided test data in the form of a datastep, this is just untested theory:

data want;
  set have;
  array group{5} 8;
  select;
    when (age1 < 12) group1=1;
    when (12 <= age1 and age2 <=60) group2=1;
    ...
    otherwise;
  end;
run;

Re: if then statement to create new variables of ranges

starz4ever2007 — Thu, 09 Feb 2017 16:39:21 GMT

I apologize (here is the test data)

data work;

input age1 age2;

datalines;

13 60

50 70

168 780

0 0

0 240

all all

192 older

run;

the code you suggested is something I tried as a function in basic Excel before and did not work. The statement......

when (12 <= age1 and age2 <=60) group2=1;

would not work as the second row would not get a "1" in group2 AND group3 even though it should because 50-70 should fall into both groups....

Re: if then statement to create new variables of ranges

RW9 — Thu, 09 Feb 2017 16:53:41 GMT

If you run that datastep you will see there are errors as you have number and character (in the input you are reading number as no $). I have removed the invalid data and present:

data work;
  input age1 age2;
datalines;
13     60            
50     70
168   780
0       0
0      240
;
run;

data want;
  set work;
  array group{5} 8;
  group1=ifn(12 <= age1 and age2 <= 60,1,0);
  group2=ifn(61 <= age1 and age2 <= 132,1,0);
  ...
run;

Re: if then statement to create new variables of ranges

Tom — Thu, 09 Feb 2017 18:40:04 GMT

So it looks like your source data has the age vaues as text instead of numbers. That will make it harder to compare. How about if we convert your 'all' and 'older' (and also 'younger') into extremely low and high values so that we can make a numeric variable out of the ages. Then the logic for telling if the two ranges overlap is pretty simple.

data work;
  length text_age1 text_age2 $10 age1 age2 group1-group5 8;
  input text_age1 text_age2;
  if text_age1 in ('younger','all') then age1=-1;
  else age1=input(text_age1,10.);
  if text_age2 in ('older','all') then age2=1000;
  else age2=input(text_age2,10.);
  group1 = (              age1 <= 12) ;
  group2 = (age2 >  12 and age1 <= 60);
  group3 = (age2 >  60 and age1 <=132);
  group4 = (age2 > 132 and age1 <=204);
  group5 = (age2 > 204);
datalines;
13     60
50     70
168   780
0       0
0      240
all     all
192   older
;;;;

proc print; run;

Re: if then statement to create new variables of ranges

gracynnrose — Mon, 24 Feb 2020 21:11:04 GMT

I also am having the same problem and do not understand how to it. I am trying to use the bg variable which is blood glucose lvl. I want the normal type to be 70-105 bg and abnormal to be all other numbers.

data wb_bg ;
SET wb_bg_1 wb_bg_2 ;
DC= '28jun02' ;
TC= '08:40' ;
DOW=weekday(date);
if 70<- bg <-105 then Type= 'A' ;
else if bg= 70-105 then Type= 'N' ;
run;

proc format ;
value DOW 1= 'Sun' 2='Mon' 3='Tue' 4='Wed' 5= 'Thu' 6= 'Fri' 7= 'Sat' ;
value Type 'A'= 'Abnormal' 'N'= 'Normal' ;
run;

proc print data=wb_bg;
format Date date7. Time time5. DOW DOW. Type Type.;
run;

Re: if then statement to create new variables of ranges

PaigeMiller — Mon, 24 Feb 2020 21:24:56 GMT

if 70<- bg <-105 then Type= 'A' ;

I don't think this is what you really want to type ... this means if -bg is between 70 and -105 then TYPE='A'; Then you type

if bg= 70-105 then Type= 'N' ;

Since 70-105 is -35, then this is the same as

if bg= -35 then type='N';

which probably isn't what you want either.

While it's not clear to me exactly what you do want, maybe something like this would work

proc format ;
value DOW 1= 'Sun' 2='Mon' 3='Tue' 4='Wed' 5= 'Thu' 6= 'Fri' 7= 'Sat' ;
value Type 70-105='Normal' other= 'Abnormal';
run;

data wb_bg;
     set wb_bg_1 wb_bg_2;
    dc='28jun02'd;
    tc='08:40't;
    dow=weekday(date);
    format bg type. dow dow.;
run;

proc print;
run;