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!
Wrong parenthesis, it should be:
first=substr(upcase(cohort), 1, 3);
PG
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;
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
Wrong parenthesis, it should be:
first=substr(upcase(cohort), 1, 3);
PG
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!
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
Thanks! For some reason, it doesn't work. No SAS Log errors.
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
Appreciate you help. It's still not working for me and no log errors..Any suggestions?
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;
Appreciate your quick reply. Can you explain why the count= 0. is inside the do loop? I will try this. Thank you!
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.
Oh. Got it! Thank you!!
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 !!
Thank you!!
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.