• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 874
  • Last Modified:

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
0
morinia
Asked:
morinia
2 Solutions
 
DiaphanosomaCommented:
Sort the data by member and date.
then do a data step like:

data want; set have;
by member date;
sequence =_n_;
run;
0
 
ShannonEECommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now