DATA Step, Macro, Functions and more

Creating a successively increasing ID

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

Creating a successively increasing ID

G'day.

Appreciate any help you can give.

I'm in need of creating two successively increasing IDs for two cohorts:

Have:

Cohort      Facility  

random     Emerson

random     Olive

random     Inglewood

ETC...

High          Montessori

High          CALIVA

ETC..

Need a new ID starting with RAN001 for the random cohort all the way up to  RAN280

Also need a new ID starting with HIG001 for the 'High' cohort all the way up to HIG060

Cohort      Facility            NEWID

random     Emerson           RAN001

random     Olive                 RAN002

random     Inglewood          RAN003

etc...                                  RAN280

High          Montessori        HIG001

High          CALIVA            HIG002

etc...                                  HIG060

Any help you can give is appreciated!


Accepted Solutions
Solution
‎02-06-2014 02:29 PM
Respected Advisor
Posts: 4,925

Re: Creating a successively increasing ID

Wrong parenthesis, it should be:

first=substr(upcase(cohort), 1, 3);


PG

PG

View solution in original post


All Replies
Super User
Posts: 19,814

Re: Creating a successively increasing ID

data want;

set have;

by cohort;

retain first count;

if first.cohort then do;

      first=substr(upcase(cohort, 1,3));

      count=0;

end;

count+1;

newid=cats(first, put(count, z3.));

run;

Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

Appreciate your quick reply.

I tried that but got the following error messages:

245  data want;

246

247  set have;

248

249  by cohort;

250

251  retain first count;

252

253  if first.cohort then do;

254

255        first=substr(upcase(cohort, 1,3));

                        ------

                        72

                 ------

                 71

ERROR 72-185: The UPCASE function call has too many arguments.

ERROR 71-185: The SUBSTR function call does not have enough arguments.

256

257        count=0;

258

259  end;

260

261  count+1;

262

263  newid=cats(first, put(count, z3.));

264

265  run

Solution
‎02-06-2014 02:29 PM
Respected Advisor
Posts: 4,925

Re: Creating a successively increasing ID

Wrong parenthesis, it should be:

first=substr(upcase(cohort), 1, 3);


PG

PG
Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

That works!

What if I wanted the newID for the random cohort to start with SRL and the new ID for the High cohort to start with PRL?

Appreciate your help!

Respected Advisor
Posts: 4,925

Re: Creating a successively increasing ID

Then you could use (untested) :

proc format;

value $cohort

"RANDOM"="SRL"

"HIGH"="PRL"

OTHER="XXX";

run;

data want;

set have;

by cohort;

retain first count;

if first.cohort then do;

      first=put(upcase(cohort), $cohort.);

      count=0;

end;

count+1;

newid=cats(first, put(count, z3.));

run;

PG

PG
Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

Thanks!  For some reason, it doesn't work. No SAS Log errors.

Respected Advisor
Posts: 4,925

Re: Creating a successively increasing ID

Some testing led to a bit of tweaking. This works (changes in red) :

data have;

input Cohort $ Facility $;

datalines;

random     Emerson

random     Olive

random     Inglewood

High       Montessori

High       CALIVA

;

proc format;

value $cohort

"RANDOM"="SRL"

"HIGH"="PRL"

OTHER="XXX";

run;

data want;

set have;

by cohort notsorted;

retain first count;

if first.cohort then do;

    first=put(upcase(cohort), $cohort.);

    count=0;

  end;

count+1;

newid=cats(first, put(count, z3.));

drop first count;

run;

proc print data=want noobs; run;

PG

PG
Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

Appreciate you help. It's still not working for me and no log errors..Any suggestions?

Super User
Posts: 19,814

Re: Creating a successively increasing ID

Hard code it. I didn't check to see if this matches your data, but the result should be the same.

data want;

set have;

by cohort;

retain first count;

if first.cohort then do;

     if cohort="random" then first="PRL";

     else if cohort="high" then first="SRL";

      count=0;

end;

count+1;

newid=cats(first, put(count, z3.));

run;

Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

Appreciate your quick reply. Can you explain why the count= 0.  is inside the do loop?   I will try this.  Thank you!

Super User
Posts: 19,814

Re: Creating a successively increasing ID

It isn't a loop, it is conditional code that executes the first time you encounter an ID. The BY variable creates an automatic indicators for first. and last.

So the first time you see the ID you need to reassign variables.  It's set to 0, because the code after increments it.

http://www.lexjansen.com/nesug/nesug97/coders/scerbo.pdf

Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

Oh. Got it!  Thank you!!

Contributor
Posts: 22

Re: Creating a successively increasing ID

Try this

data h1;

input a1 $ a2 $;

cards;

random Emerson

random Olive

random Inglewood

High Montessori

High CALIVA

;

run;

proc sort data=h1;by a1;run;

data h2;

set h1;

by a1;

retain c 0;

c+1;

if first.a1 then do;

c=1;

end;

if a1='random' then do;

final='SRL' || trim(put(c,z3.));

end;

else if a1='High' then do;

final='PRL' || trim(put(c,z3.));

end;

run;

Hope this helps !!

Regular Contributor
Posts: 194

Re: Creating a successively increasing ID

Posted in reply to yeshwanth

Thank you!!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 561 views
  • 6 likes
  • 4 in conversation