duplicate observations - how to transform the them to unique observations?

[Maxi74]

Hi,
I am a SAS beginner.
My dataset with 4,000 observations looks like this
id startyear endyear exposure1 exposure 2 exposure 3
id1 1985 1987 1 0 0
id2 1992 1997 1 0 0
id2 1998 1999 0 1 0
id2 2000 2007 0 0 1
id3 1996 2007 0 0 1
id4 1987 2008 1 0 1

The ids are duplicated if the id (person) has reported different time periods for exposures.
I need to calculate the cumulative exposure, and I need the ids unique, but keeping the exposure information (to be able to sum them). How can I transform the data so that different exposures in different time periods become variables instead of different observations?
(Maybe the columns looks weird, but the 1 and 0 are supposed to be under the column headings "exposure1.. etc")

Thanks,
Maxi74

[sbb]

Look at using PROC TRANSPOSE.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

proc transpose site:sas.com

[ariari]

hi Maxi74
other way of the solution:
data x;
input id $ startyear endyear exposure1 exposure2 exposure3 ;
cards;
id1 1985 1987 1 0 0
id2 1992 1997 1 0 0
id2 1998 1999 0 1 0
id2 2000 2007 0 0 1
id3 1996 2007 0 0 1
id4 1987 2008 1 0 1
;
run ;

data x ;
set x ;
new_id=substr(compress(id),1,2) ;
proc sort ; by new_id ;
run ;

data res ;
set x ;
by new_id ;
if first.new_id then sum=exposure1;
else sum+exposure1;
if last.new_id then output;
run ;


Irena

[deleted_user]

hello,

if your goal is to get a report with total exposures per id you can try the report procedure (which will also can create a data set):

[pre]
data x;
input id $ startyear endyear exposure1 exposure2 exposure3 ;
cards;
id1 1985 1987 1 0 0
id2 1992 1997 1 1 0
id2 1998 1999 0 1 0
id2 2000 2007 0 0 1
id3 1996 2007 0 0 1
id4 1987 2008 1 0 1
;
run ;

proc report data=x out=in_case nowd;
column id exposure1 exposure2 exposure3 total;
define id / group;
compute total;
total=sum (exposure1.sum,exposure2.sum,exposure3.sum);
endcomp;
run;
[/pre]

Marius

[Ksharp]

Hi.
Your words are ambiguous. Or I can not understand what your mean totally.
What output you want to look like?
And How to define the duplicated obs (give an example will be very helpful),
What do you do with these duplicated obs?


Ksharp