topic flagging dups on multiple vars in SAS Procedures

flagging dups on multiple vars

deleted_user — Fri, 17 Apr 2009 17:17:44 GMT

Hello,
I'm in the early days of converting from SPSS to SAS.

My file has the first four columns. I want to add the last two columns--LAST and COUNT.

LAST indicates if a record is unique based on id, v2, and flag.
COUNT would be a cumulative count based on id and v2 ordered by date where 0 means unique record.

id v2 flag date LAST COUNT
1, a, 0, 1/1/2008, 1, 1
1, a, 1, 1/7/2009, 1, 2
1, b, 1, 1/1/2009, 1, 0
1, c, 0, 1/1/2007, 0, 1
1, c, 0, 1/1/2008, 1, 2
1, c, 1, 1/1/2009, 1, 3
1, d, 0, 6/1/2007, 1, 0

I'm totally stumped on how to create last. I think I've figured out a really inelegant way to get the count var, but an easier way would be appreciated.

Thanks for any help!

Dana

Re: flagging dups on multiple vars

sbb — Fri, 17 Apr 2009 17:23:56 GMT

The SAS DATA step and using FIRST. and LAST. against your sorted file, and specifying the required BY statement with your variable list. The SAS support http://support.sas.com/ website has DOC and supplemental technical papers.

Scott Barry
SBBWorks, Inc.

How the DATA Step Identifies BY Groups
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a000761931.htm

Re: flagging dups on multiple vars

deleted_user — Fri, 17 Apr 2009 18:14:03 GMT

Hello lockwoo,

Could this help?

yoba

data T01_input;
length v2 $1;
infile cards delimiter=',' firstobs=2;
input id v2 flag date last count;
informat date ddmmyy10.; format date date9.;
cards;
id, v2, flag, date, LAST, COUNT
1, a, 0, 1/1/2008, 1, 1
1, a, 1, 1/7/2009, 1, 2
1, b, 1, 1/1/2009, 1, 0
1, c, 0, 1/1/2007, 0, 1
1, c, 0, 1/1/2008, 1, 2
1, c, 1, 1/1/2009, 1, 3
1, d, 0, 6/1/2007, 1, 0
;
run;

proc sort data=T01_input;
by id v2 flag;
run;

data T02_temp;
set T01_input;
by id v2 flag;
if first.flag=last.flag=1 then LAST2=1; else LAST2=0;
run;

proc sort data=T02_temp;
by id v2 date;
run;

data T03_output;
set T02_temp;
by id v2 date;
if first.V2 then COUNT2=0;
if not (first.V2=last.V2=1) then COUNT2+1;
run;

proc print data=T03_output;
run;

Re: flagging dups on multiple vars

deleted_user — Fri, 17 Apr 2009 18:57:58 GMT

Terrific! I knew it had to be easier than I was making it.
Thank you thank you

Dana