Adding sequence numbers to input records in SAS

Posted on 2011-10-18
Last Modified: 2013-11-16
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.
Question by:morinia
    LVL 1

    Accepted Solution

    Sort the data by member and date.
    then do a data step like:

    data want; set have;
    by member date;
    sequence =_n_;
    LVL 8

    Assisted Solution


    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;

    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;

    Open in new window

    Hope this explains what you need to do.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction After workin in a plethora of programming languages like C, Pro*C, ESQL/C, C++, VC++, VB, Java, HTML,JavaScipt etc, technologies and frameworks like JSP, Servlets, Struts, Spring, IBatis etc and databases like MS Access, SQLServer, Inf…
    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now