topic Re: Creating a successively increasing ID in SAS Programming

Creating a successively increasing ID

jcis7 — Wed, 29 Jan 2014 21:34:00 GMT

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!

Re: Creating a successively increasing ID

Reeza — Wed, 29 Jan 2014 22:01:41 GMT

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;

Re: Creating a successively increasing ID

jcis7 — Thu, 06 Feb 2014 16:57:33 GMT

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));

------

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

Re: Creating a successively increasing ID

PGStats — Thu, 06 Feb 2014 19:29:02 GMT

Wrong parenthesis, it should be:

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

Re: Creating a successively increasing ID

jcis7 — Thu, 06 Feb 2014 20:14:06 GMT

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!

Re: Creating a successively increasing ID

PGStats — Thu, 06 Feb 2014 21:40:58 GMT

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;

Re: Creating a successively increasing ID

jcis7 — Wed, 26 Feb 2014 01:30:07 GMT

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

Re: Creating a successively increasing ID

PGStats — Wed, 26 Feb 2014 03:00:24 GMT

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;

Re: Creating a successively increasing ID

jcis7 — Thu, 27 Feb 2014 20:36:44 GMT

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

Re: Creating a successively increasing ID

Reeza — Thu, 27 Feb 2014 21:22:18 GMT

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;

Re: Creating a successively increasing ID

yeshwanth — Thu, 27 Feb 2014 21:44:15 GMT

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 !!

Re: Creating a successively increasing ID

PGStats — Thu, 27 Feb 2014 22:22:20 GMT

How do you know it didn't work?

Re: Creating a successively increasing ID

jcis7 — Fri, 28 Feb 2014 21:21:37 GMT

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

Re: Creating a successively increasing ID

Reeza — Fri, 28 Feb 2014 21:27:37 GMT

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

Re: Creating a successively increasing ID

jcis7 — Wed, 05 Mar 2014 19:50:34 GMT

It didn't change the first three letters of the newid.

Re: Creating a successively increasing ID

jcis7 — Wed, 05 Mar 2014 19:50:58 GMT

Oh. Got it! Thank you!!

Re: Creating a successively increasing ID

jcis7 — Wed, 05 Mar 2014 19:51:36 GMT

Thank you!!