if then statement to create new variables of ranges

[starz4ever2007]

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!

[RW9]

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;

[starz4ever2007]

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

[RW9]

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;

[Tom]

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;

[gracynnrose]

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;

[PaigeMiller]

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;