BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
Hi all,
My SAS dataset has three columns ID,age,gender.How to I bring all three values into a single column one below the other.
For instance:

From:

ID age gender
11 25 Male

to :

New_column
11
25
Male
11 REPLIES 11
deleted_user
Not applicable
data have;
input ID age gender $;
cards;
11 25 Male
;

proc transpose data=have out=want(drop=_name_ rename=(col1=New_column));
var id age gender;
run;

data want(drop=i);
set want;
array tmp
  • _all_;
    do i=1 to dim(tmp);
    tmp=strip(tmp);
    end;
    run;
  • deleted_user
    Not applicable
    data have;
    input ID age gender $;
    cards;
    11 25 Male
    ;

    data want(keep=New_column);
    set have;
    array num
  • _numeric_;
    array char
  • _character_;
    length New_column $ 10;
    do i=1 to dim(num);
    New_column=strip(put(num,best.));
    output;
    end;
    do j=1 to dim(char);
    New_column=strip(char);
    output;
    end;
    run;
  • sbb
    Lapis Lazuli | Level 10 sbb
    Lapis Lazuli | Level 10
    To generate the output display shown be the OP, it requires a DATA step most likely - use a SAS PUT statement to generate the "vertical" variable value display. It is not necessary to create a new column, if you only want to display the information vertically instead of horizontally.

    PUT 'header_info' / var1 / var2 / var3 ;

    Scott Barry
    SBBWorks, Inc.
    data_null__
    Jade | Level 19
    Here is a variation on your program that you may find interesting. Can you see the modest advantage over using ARRAYS? I can think of 2 advantages.

    We still have to insure the receiving variable has the proper length. PROC TRANSPOSE will handle that for us which is an advantage with PROC TRANSPOSE.

    [pre]
    data have;
    input ID $ age gender $;
    cards;
    11 25 Male
    12 24 FeMale
    13 25 Male
    ;;;;
    run;

    data tall(keep=Column);
    set have;
    length _name_ $32 Column $12;
    do while(1);
    call vnext(_name_);
    if _name_ eq '_name_' then leave;
    column = left(vvalueX(_name_));
    output;
    end;
    run;

    proc print;
    run;
    [/pre]
    sbb
    Lapis Lazuli | Level 10 sbb
    Lapis Lazuli | Level 10
    data _null_ -- Swwwweet!

    Scott
    SASPhile
    Quartz | Level 8
    Hi data _null_,
    instead of getting the columns as different obeservations, the user needs them as single record for each row of data.
    for instance:
    data have;
    input ID $ age gender $;
    cards;
    11 25 Male
    12 24 FeMale
    13 25 Male;;;;
    run;

    The output should look like(3 records, instead of 9):
    11
    12
    male

    12
    24
    female

    13
    25
    male

    the above display is actually three rows only.NOT 9

    thanks
    Cynthia_sas
    SAS Super FREQ
    Hi:
    To me, it almost sounds like you want a line feed or carriage return in your display??? I would interpret this as being that you are moving into the world of REPORTS and procedure output (such as PROC PRINT/PROC REPORT), because SAS datasets do NOT have line feeds and carriage returns.

    What would be your procedure of choice to create this report??? And what would be your ODS destination of choice?? HTML, RTF or PDF???

    cynthia
    SASPhile
    Quartz | Level 8
    Cynthia,
    The choices are proc report and rtf.
    Cynthia_sas
    SAS Super FREQ
    Hi:
    Consider using ODS ESCAPECHAR, as described in these papers (some of them show 9.1.3 version of ESCAPECHAR syntax and some of them show the 9.2 ESCAPECHAR syntax):
    http://www2.sas.com/proceedings/forum2007/099-2007.pdf
    http://www2.sas.com/proceedings/sugi31/227-31.pdf
    http://www2.sas.com/proceedings/forum2007/144-2007.pdf
    http://www.nesug.org/Proceedings/nesug07/cc/cc18.pdf
    https://www.abtassoc.org/presentations/lsh3_2009.pdf
    http://www.nesug.org/proceedings/nesug08/np/np10.pdf

    Or RTF control strings (such as \line) as shown in the example below.

    to insert a LINE FEED or "return" into a character variable. In the first example below, I use a DATA step program to create a variable called STKVAR -- which contains the concatenated NAME, SEX, AGE and HEIGHT variable information from SASHELP.CLASS

    Note that there are 4 "lines" in every row of the report. Example 1 uses a DATA step to build STKVAR and RTFCTL; Example 2 uses PROC REPORT and a COMPUTE block and NOPRINT variables to build VAR1 and VAR2; and both examples also show the use of RTF controls strings instead of using ODS ESCAPECHAR methods.

    cynthia
    [pre]
    data class;
    length stkvar $75 rtfctl $75;
    set sashelp.class(obs=3);
    stkvar = catx('~n',name,sex,put(age,2.0),put(height,5.1));
    rtfctl = catt(name,'\line{',sex,'}\line{',put(age,2.0),'}\line{',put(height,5.1),'}');
    run;

    options nodate nonumber;
    ods listing close;
    ods rtf file='make_stacked_report_col1.rtf';
    ods escapechar='~';

    ** listing individual vars here to show line feeds inserted correctly;
    ** cellwidth set to show effect of just=c;
    proc report data=class nowd split='*';
    title '1)Using ODS ESCAPECHAR To Put a Line Feed into a REPORT Column';
    title2 'Also using RTF control strings to put a "\line" command into a cell';
    column name sex age height stkvar rtfctl;
    define stkvar / "Use ESCAPECHAR*Method"
    style(column)={just=c cellwidth=1.5in};
    define rtfctl / "Use RTF*Control String"
    style(column)={just=c protectspecialchars=off cellwidth=1.5in};
    run;

    ods rtf close;

    ods rtf file='make_stacked_report_col2.rtf';
    ods escapechar='~';

    ** Using NOPRINT here to hide cols used to build col with line feed;
    ** kept cellwidth to show just=c;
    proc report data=sashelp.class(obs=3) nowd split='*';
    title '2)Making all the report columns in PROC REPORT';
    title2 'All the variables used to build the new report item are NOPRINT';
    title3 'But they do not need to be NOPRINT items';
    column name sex age height var1 var2;
    define name / order noprint;
    define sex / display noprint;
    define age / display noprint;
    define height / display noprint;
    define var1 / computed "Use ESCAPECHAR*Method"
    style(column)={just=c cellwidth=1.5in};
    define var2 / computed "Use RTF*Control String"
    style(column)={just=c protectspecialchars=off cellwidth=1.5in};
    compute var1 / character length=75;
    var1 = catx('~n',name,sex,put(age,2.0),put(height,5.1));
    endcomp;
    compute var2 / character length=75;
    var2 = catt(name,'\line{',sex,'}\line{',put(age,2.0),'}\line{',put(height,5.1),'}');
    endcomp;
    run;

    ods rtf close;


    [/pre]
    data_null__
    Jade | Level 19
    I don't get it.
    deleted_user
    Not applicable
    I think we're not getting enough of the story.

    But, to keep things simple.

    [pre]
    data _null_;
    file "output";
    set indata;
    put "Column name" / ID $ / age / gender $ ;
    run;
    [/pre]

    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
    • 11 replies
    • 1723 views
    • 0 likes
    • 5 in conversation