topic Re: duplicate observations - how to transform the them to unique observations? in SAS Procedures

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

Maxi74 — Sat, 19 Feb 2011 18:42:28 GMT

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

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

sbb — Sat, 19 Feb 2011 20:29:22 GMT

Look at using PROC TRANSPOSE.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

proc transpose site:sas.com

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

ariari — Sun, 20 Feb 2011 07:47:40 GMT

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

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

deleted_user — Sun, 20 Feb 2011 16:11:44 GMT

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

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

Ksharp — Mon, 21 Feb 2011 02:43:21 GMT

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