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.
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
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;
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;
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.
proc sql;
select *, MONOTONIC() as CONSNUMBER
from have;
group by var1-var5;
quit;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.