BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Wrong parenthesis, it should be:

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


PG

PG

View solution in original post

16 REPLIES 16
Reeza
Super User

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;

jcis7
Pyrite | Level 9

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

PGStats
Opal | Level 21

Wrong parenthesis, it should be:

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


PG

PG
jcis7
Pyrite | Level 9

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!

PGStats
Opal | Level 21

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
jcis7
Pyrite | Level 9

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

PGStats
Opal | Level 21

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
jcis7
Pyrite | Level 9

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

Reeza
Super User

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;

jcis7
Pyrite | Level 9

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

Reeza
Super User

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

jcis7
Pyrite | Level 9

Oh. Got it!  Thank you!!

yeshwanth
Fluorite | Level 6

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

jcis7
Pyrite | Level 9

Thank you!!

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
  • 16 replies
  • 1745 views
  • 6 likes
  • 4 in conversation