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

I want to add serial number to the unique values of each group. See the snapshot below-

 

 

data xyz;
input x $;
cards;
AA
AA
AA
BB
BB
;
run;

proc sql;
select a.x, b.N from xyz a
inner join
(select x, monotonic() as N
from (
select distinct x
from xyz)) b
on a.x=b.x;
quit;

 

 

How we can do it with data step?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Close but no cigar:

 

data temp;
set xyz;
by x;
retain z 0;
if first.x then z+1;
run;

proc print;
run;

View solution in original post

8 REPLIES 8
Reeza
Super User

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

 

Use BY group processing, see a detailed write up in the link above.

Ujjawal
Quartz | Level 8

I have tried but didn't get any success.
data temp;
set xyz;
if first.x then z=1;
else z+1;
by x;
proc print;
run;

It increments the value. It's not my desired output.

Reeza
Super User

Close but no cigar:

 

data temp;
set xyz;
by x;
retain z 0;
if first.x then z+1;
run;

proc print;
run;
Ujjawal
Quartz | Level 8

Thanks a ton. It works like a charm. How it works? I failed to understand how it increments with "if first.x then z+1;" and how it keeps the same value to values of a same group?

ballardw
Super User

RETAIN is a datastep instruction that says to keep the value of a variable from record to another. This will not typically work if the variable(s) named in the Retain statement exist in a data set on the SET statement as they would be read from the set.

 

Using a statement like:

 

Z+1; /* note there is no = in the assignment*/

implies that Z should be retained.

Ujjawal
Quartz | Level 8

Thanks for your explanation. I am confused why ELSE statement was not required. See my code below- 

 

data example22;
set xyz;
if first.x then N+1;
else N=N;
by x;
proc print;
run;

ballardw
Super User

Because you wanted the same value of the counter for all records with the same value of x you don't want an Else. The retain keeps the value once assigned.

The ELSE clause would be for something you only want to do for records that are not the first.x.

 

See this thread for one example of Else with First.var processing to do something just a little more complicated that your problem:

https://communities.sas.com/t5/SAS-Data-Management/Creating-a-counter-for-the-same-hospitalization/m...

 

Reeza
Super User

BY group processing:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a001283274.htm

 

RETAIN

keeps the value from row to row

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
  • 8 replies
  • 1290 views
  • 1 like
  • 3 in conversation