SAS Rows to Columns

Posted on 2009-04-23
Last Modified: 2013-11-16
I need to do the following in "SAS"  (Also the number of Meds will vary. So there might be 3, 10, 20, etc)
Take the example dataset from
ID Med
1    A  
1    B
1    C
2    D
3    A
3    C

ID   Med_1   Med_2   Med_3  
1      A             B         C
2      D
3      A             C

Question by:flyfishin4trout
    LVL 4

    Accepted Solution

    Use Proc Transpose

    I would change your ID variable to something else, since ID is a SAS keyword.  I used new_id here.

    proc transpose data=test1 out=test2 prefix=med_;
    by new_id;
    ID new_id;
    var med;

    May not be 100% accurate, but this is basically what you need.

    Here are a couple of links that may help:

    Expert Comment

    Hi J

    Here is the answer. Its simple code and exact solution to your problem. It creates "mid_" columns based on the values of ID column. If ID column has 10 unique values then there will be 10 "mid_" variables in the result dataset.

    proc transpose data=sample out=result prefix=mid_;
    by id;
    var med;
    If the dataset "sample" is not sorted:
    proc sort data=sample;
    by id;
    proc transpose data=sample out=result prefix=mid_;
    by id;
    var med;

    Open in new window

    LVL 4

    Expert Comment

    Proc transpose is definitely what he needed as was my suggestion.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    "Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
    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…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now