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!
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;
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....
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;
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;
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;
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;
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: