Adding sequence numbers to input records in SAS

I have an input file that has multiple dates coming in for one member.  I would like to add sequence numbers to the end of the record for use in later steps.

How do I add the sequence number.  (See attached sheet).  I would like to add the sequence number to the column at the right.  I am trying to calculate the numbers in red.
Transpose-A.xlsx
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
DiaphanosomaConnect With a Mentor Commented:
Sort the data by member and date.
then do a data step like:

data want; set have;
by member date;
sequence =_n_;
run;
0
 
IanConnect With a Mentor StatisticianCommented:
Morinia,

If your data is already sorted by   member_id  (as in the example dataset) then

*  Data step to add sequence number *;
data   ExitDates;
      set   ExitDates;
      by    member_id;

      retain   SeqNo;

** Reset the sequence number for each member *;
if  first.member_id then SeqNo = 0;

** Increment sequence number for each record **;
SeqNo = SeqNo + 1;

run;

Open in new window


This code will add the variable  SeqNo at the end of the program record and restart the numbering  to 1 at each new member.  SAS will check that the file is sorted by Member Id and will complain (and stop) if not in sorted order.

The output will overwrite the input (assumed here to be called ExitDates), but only if the data step completed without error.

If the dataset is not sorted then put the following in front of the code above

*  procedure step to sort the data into member order *;
proc sort data= ExitDates;
      by    member_id;
run;

Open in new window


Hope this explains what you need to do.

Ian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.