topic Re: Observations with same field values in SAS Programming

Observations with same field values

Avenue — Tue, 22 Jul 2014 14:09:25 GMT

Hi Guys,

Below is the transformation rule I am working on. Basically, some records are created with the same field values such as the fields highlighted below:

If this is the case, then create another field CONSNUMBER and set the first record’s value to “001”, second to “002”, third to “003” and so on.

700 1033935598 1033935589 TEL 01JAN2001 E Daytime Only

700 1033935598 1033935589 TEL 01JAN2001 E Anytime

I have tried multiple approaches but they all haven’t worked for. Any suggestions will be greatly appreciated.

Re: Observations with same field values

PGStats — Tue, 22 Jul 2014 14:26:29 GMT

Assuming your duplicated fields are named var1 to var5 :

proc sort data=have; by var1-var5; run;

data want;

set have; by var1-var5;

if first.var5 then consnumber = 0;

consnumber + 1;

run;

Re: Observations with same field values

RW9 — Tue, 22 Jul 2014 14:28:19 GMT

Sorry, could you provide some test data and required output data as I can't tell from the above what you are trying to do, do you mean something like:

700 1033935598 1033935589 TEL 01JAN2001 E Daytime Only 001

700 1033935598 1033935589 TEL 01JAN2001 E Anytime 002

Then that's straight forward (assuming sorted):

data want;

set have;

by var1 var2 var3;

retain count;

if first.var1 then count=1;

else count=count+1;

newvar=put(count,z3.);

run;

Re: Observations with same field values

slchen — Tue, 22 Jul 2014 14:30:01 GMT

This is what you are after?

data want(drop=n);
set have;
by id;
if first.id then n=0;
n+1;
consnumber=put(n,z3.);
run;

Re: Observations with same field values

Avenue — Tue, 22 Jul 2014 14:38:38 GMT

Thanks guys. Just picked up something after implementing the code you all suggested. See below:

1033162957	1033162952	TEL	01JAN2001	E	Anytime	001
1033162957	1033162952	TEL	01JAN2001	E	Daytime Only	002
1033162957	1033162952	PAG	01JAN2001	E	Anytime	003

The last record has a value PAG in the third field and I will like it to remain 001. In essecnce, the sequence will be 001,002,001 because the field is different in a way.

If say we had 2 of each record, that is 2 records with TEL and 2 records with PAG then we should have 001,002,001,002.

Sorry I was not aware of this case.

Thanks a lot guys.

Re: Observations with same field values

stat_sas — Tue, 22 Jul 2014 14:39:28 GMT

proc sql;

select *, MONOTONIC() as CONSNUMBER

from have;

group by var1-var5;

quit;

Re: Observations with same field values

Avenue — Tue, 22 Jul 2014 14:52:26 GMT

Thanks guys, I have the solution. So instead of looking and my primary sorting variable, i used a variable that changes.

1033194759	1033194752	PAG	01JAN2001	E	Anytime	001
1033194759	1033194752	TEL	01JAN2001	E	Anytime	001
1033194759	1033194752	TEL	01JAN2001	E	Daytime Only	002

data want;

set have;

by var1 var2 var3;

format CONSNUMBER $3.;

retain count;

if first.var3 then

count=1;

else count=count+1;

CONSNUMBER = put(count,z3.);

;

run;