Observations with same field values

Reply
Occasional Contributor
Posts: 13

Observations with same field values

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.

Respected Advisor
Posts: 4,609

Re: Observations with same field values

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;

PG

PG
Esteemed Advisor
Esteemed Advisor
Posts: 7,233

Re: Observations with same field values

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;

Super Contributor
Posts: 275

Re: Observations with same field values

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;

Occasional Contributor
Posts: 13

Re: Observations with same field values

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

10331629571033162952TEL01JAN2001EAnytime001
10331629571033162952TEL01JAN2001EDaytime Only002
10331629571033162952PAG01JAN2001EAnytime003

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.

Trusted Advisor
Posts: 1,203

Re: Observations with same field values

proc sql;

select *, MONOTONIC() as CONSNUMBER

from have;

group by var1-var5;

quit;

Occasional Contributor
Posts: 13

Re: Observations with same field values

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

10331947591033194752PAG01JAN2001EAnytime001
10331947591033194752TEL01JAN2001EAnytime001
10331947591033194752TEL01JAN2001EDaytime Only002

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;

Ask a Question
Discussion stats
  • 6 replies
  • 196 views
  • 2 likes
  • 5 in conversation