topic Re: Bring three columns into one in SAS Programming

Bring three columns into one

SASPhile — Fri, 26 Mar 2010 03:29:25 GMT

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

Re: Bring three columns into one

deleted_user — Fri, 26 Mar 2010 05:13:56 GMT

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;

Re: Bring three columns into one

deleted_user — Fri, 26 Mar 2010 05:27:16 GMT

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;

Re: Bring three columns into one

sbb — Fri, 26 Mar 2010 11:12:44 GMT

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.

Re: Bring three columns into one

data_null__ — Fri, 26 Mar 2010 13:38:04 GMT

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]

Re: Bring three columns into one

sbb — Fri, 26 Mar 2010 13:41:04 GMT

data _null_ -- Swwwweet!

Scott

Re: Bring three columns into one

SASPhile — Fri, 26 Mar 2010 20:28:22 GMT

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

Re: Bring three columns into one

Cynthia_sas — Fri, 26 Mar 2010 20:59:14 GMT

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

Re: Bring three columns into one

SASPhile — Sat, 27 Mar 2010 01:10:56 GMT

Cynthia,
The choices are proc report and rtf.

Re: Bring three columns into one

data_null__ — Sat, 27 Mar 2010 01:35:12 GMT

I don't get it.

Re: Bring three columns into one

Cynthia_sas — Sat, 27 Mar 2010 01:46:34 GMT

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]

Re: Bring three columns into one

deleted_user — Wed, 31 Mar 2010 14:29:18 GMT

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]