- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi all,
I am hoping to get your help with the following:
I have a database that looks like this
year | sex | are a | status | incidence | prevalence |
2000 | F | ON | 0 | 5 | 200 |
2001 | F | ON | 0 | 6 | 300 |
2002 | F | ON | 0 | 5 | 350 |
2003 | F | ON | 0 | 5 | 201 |
2004 | F | ON | 0 | 4 | 222 |
2005 | F | ON | 0 | 2 | 333 |
and I want to 'mush' together rows to get this :
year | sex | are a | status | incidence | prevalence |
2000/2002 | F | ON | 0 | 16 | 850 |
2003/2005 | F | ON | 0 | 11 | 756 |
Note that I am simply adding the years. I have several thousands of these so doing it by hand in excel is not very practical.
Any idea of how to do this in SAS?
Thank you in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@K_S wrote:
Hi all,
I am hoping to get your help with the following:
I have a database that looks like this
year sex are a status incidence prevalence 2000 F ON 0 5 200 2001 F ON 0 6 300 2002 F ON 0 5 350 2003 F ON 0 5 201 2004 F ON 0 4 222 2005 F ON 0 2 333
and I want to 'mush' together rows to get this :
year sex are a status incidence prevalence 2000/2002 F ON 0 16 850 2003/2005 F ON 0 11 756
Note that I am simply adding the years. I have several thousands of these so doing it by hand in excel is not very practical.
Any idea of how to do this in SAS?
Thank you in advance!
"Adding the years" ... do you mean adding the incidence and prevalence within years?
You can create a format that groups together the different years, and then run PROC SUMMARY on the formatted years to get the sums.
For example:
proc format; value yearf 2000-2002='2000/2002' 2003-2005='2003/2005'; run; proc summary nway data=have; class year; format year yearf.; var incidence prevalence; output out=want sum=; run;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input year sex $ are_a $ status $ incidence prevalence;
datalines;
2000 F ON 0 5 200
2001 F ON 0 6 300
2002 F ON 0 5 350
2003 F ON 0 5 201
2004 F ON 0 4 222
2005 F ON 0 2 333
;
proc sql;
create table want(drop=k) as
select distinct catx('/',min(year),max(year)) as year, sex , are_a ,status ,round(year/3) as k, sum(incidence) as sum_incidence, sum(prevalence) as sum_prevalence
from have
group by k;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input year sex $ area $ status incidence prevalence;
cards;
2000 F ON 0 5 200
2001 F ON 0 6 300
2002 F ON 0 5 350
2003 F ON 0 5 201
2004 F ON 0 4 222
2005 F ON 0 2 333
;
proc sql noprint;
create table want1 as
select catx('/',min(year),max(year)) as year_,
sex,
area,
status,
sum(incidence) as sum_incidence1,
sum(prevalence) as sum_prevalence1
from have
where year in (2000:2002);
create table want2 as
select catx('/',min(year),max(year)) as year_,
sex,
area,
status,
sum(incidence) as sum_incidence2,
sum(prevalence) as sum_prevalence2
from have
where year in (2003:2005);
quit;
data tmp;
set want1 (rename = (sum_incidence1 = incidence sum_prevalence1 = prevalence year_ = year))
want2 (rename = (sum_incidence2 = incidence sum_prevalence2 = prevalence year_ = year));
run;
data want;
set tmp;
by year notsorted;
if last.year;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input year sex $ are_a $ status $ incidence prevalence;
datalines;
2000 F ON 0 5 200
2001 F ON 0 6 300
2002 F ON 0 5 350
2003 F ON 0 5 201
2004 F ON 0 4 222
2005 F ON 0 2 333
;
data want;
retain _year;
if 0 then set have;
sum_incidence=0;
sum_prevalence=0;
do _n_=1 by 1 until(_n_=3);
set have;
by year;
sum_incidence+incidence;
sum_prevalence+prevalence;
if _n_=1 then temp=year;
else if _n_=3 then do;
_year=catx('/',temp,year);
output;
end;
end;
drop year temp incidence prevalence;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@novinosrin wrote:
data have;
input year sex $ are_a $ status $ incidence prevalence;
datalines;
2000 F ON 0 5 200
2001 F ON 0 6 300
2002 F ON 0 5 350
2003 F ON 0 5 201
2004 F ON 0 4 222
2005 F ON 0 2 333
;
data want;
retain _year;
if 0 then set have;
sum_incidence=0;
sum_prevalence=0;
do _n_=1 by 1 until(_n_=3);
set have;
by year;
sum_incidence+incidence;
sum_prevalence+prevalence;
if _n_=1 then temp=year;
else if _n_=3 then do;
_year=catx('/',temp,year);
output;
end;
end;
drop year temp incidence prevalence;
run;
Assuming the year combinations wanted are always three-year combinations, this works fine. It also assumes that the final year is some multiple of 3 years after the initial year.
I find it opposite to my way of thinking to perform these types of summations in a data step, you have to create your own looping and end of loop conditions; when PROC MEANS and PROC SUMMARY were built to do this.
I don't know what the speed implications are for large data sets (which of course this example is not), however I would imagine that using a PROC would be faster for large data sets, but I am not aware of any study that shows this.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@PaigeMiller You are absolutely right. I would like the OP to clarify the grouping sets of years that he/she wants in the required output. I was merely having fun with Sql and datastep approaches as it didn't take more than a couple of mins. lol
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am not sure I understand what I have to clarify, so please forgive me if I am off topic.
I have about 10 years worth of data for 10 different geographic areas. I am trying to get disease prevalence estimates but inctea of yearly estimates, i want to get estimates based on 3 year avearegs.
I hope this clarifies. Also I need to keep the in-between columns, so they cannot be eliminated.
Thank you!