BookmarkSubscribeRSS Feed
Avenue
Calcite | Level 5

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.

6 REPLIES 6
PGStats
Opal | Level 21

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

slchen
Lapis Lazuli | Level 10

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;

Avenue
Calcite | Level 5

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.

stat_sas
Ammonite | Level 13

proc sql;

select *, MONOTONIC() as CONSNUMBER

from have;

group by var1-var5;

quit;

Avenue
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 583 views
  • 2 likes
  • 5 in conversation