DATA Step, Macro, Functions and more

Bring three columns into one

Reply
Super Contributor
Posts: 673

Bring three columns into one

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
N/A
Posts: 0

Re: Bring three columns into one

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;
  • N/A
    Posts: 0

    Re: Bring three columns into one

    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;
  • Super Contributor
    Super Contributor
    Posts: 3,174

    Re: Bring three columns into one

    Posted in reply to deleted_user
    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.
    Respected Advisor
    Posts: 3,799

    Re: Bring three columns into one

    Posted in reply to deleted_user
    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]
    Super Contributor
    Super Contributor
    Posts: 3,174

    Re: Bring three columns into one

    Posted in reply to data_null__
    data _null_ -- Swwwweet!

    Scott
    Super Contributor
    Posts: 673

    Re: Bring three columns into one

    Posted in reply to data_null__
    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
    SAS Super FREQ
    Posts: 8,866

    Re: Bring three columns into one

    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
    Super Contributor
    Posts: 673

    Re: Bring three columns into one

    Posted in reply to Cynthia_sas
    Cynthia,
    The choices are proc report and rtf.
    SAS Super FREQ
    Posts: 8,866

    Re: Bring three columns into one

    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]
    Respected Advisor
    Posts: 3,799

    Re: Bring three columns into one

    I don't get it.
    N/A
    Posts: 0

    Re: Bring three columns into one

    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]
    Ask a Question
    Discussion stats
    • 11 replies
    • 483 views
    • 0 likes
    • 5 in conversation