topic Re: Multiple columns into single column one after other in SAS Procedures

Multiple columns into single column one after other

sas2srinivas — Tue, 31 Mar 2015 11:22:33 GMT

Hi,

I need help. Currently I have input data like below

EMPName EMPDesi EMPSAL

Suresh SE 10000
Ramesh SSE 100000

naresh SE 20302

mahesh TL 203204

and I want create the excel file with this format

EMPName_Desi	EMPSAL
Name Suresh Desi SE	10000
Name Ramesh Desi SSE	100000
Name Naresh Desi SE	20302
Name Mahesh Desi TL	203204

Thanks

Sreenivas

Re: Multiple columns into single column one after other

jwillis — Tue, 31 Mar 2015 13:12:01 GMT

You will have to rename col1 and col2 to make the EXCEL column headers match what you need.

You will have to "wrap text" on col1 to make it fit your EXCEL layout.

data page2;

attrib col1 label = "EMPName_Desi" length = $ 60 format = $60.

col2 label = "EMPSAL" length = $ 20 format = $20.;

format EMPName $70. EMPDesi $5. EMPSAL $16.;

retain col1 col2;

input c1 $ c2 $ c3 $;

EMPName = strip(c1); EMPDesi = strip(c2); EMPSAL = strip(c3);

do i = 1 to 4;

if i = 1 then do;

col1 = ' '; col2 = ' '; /* clear the col1 and col2 retained values */

col1 = "Name";

col2 = strip(EMPSAL);

end;

if i = 2 then do;

col1 = trim(left(col1)) ||' '||trim(left(EMPName));

end;

if i = 3 then do;

col1 = trim(left(col1)) ||' '|| trim(left('Desi'));

end;

if i = 4 then do;

col1 = trim(left(col1)) ||' '|| trim(left(EMPDesi));

output; /* output only after concatenating all the EMPName and EMPDesi values into col1*/

end;

drop EMPName EMPDesi EMPSAL i c1 c2 c3 ;

datalines;;;;

Suresh SE 10000

Ramesh SSE 100000

naresh SE 20302

mahesh TL 203204

;

run;

proc print data=page2 label;run;

Re: Multiple columns into single column one after other

Ksharp — Tue, 31 Mar 2015 13:22:32 GMT

data have;
input EMPName  $ EMPDesi $ EMPSAL ;
cards;
Suresh         SE     10000
Ramesh       SSE    100000
naresh         SE       20302
mahesh       TL        203204
;
run;
data want;
 length EMPName_Desi $ 50;
 set have;
 EMPName_Desi=catx('~n','Name',EMPName,'Desi',EMPDesi);
 drop EMPName EMPDesi;
run;
ods listing close;
ods escapechar='~';
ods tagsets.excelxp file='c:\temp\x.xls' style=sasweb;
proc report data=want nowd;run;
ods tagsets.excelxp close;
ods listing;

Xia Keshan

Message was edited by: xia keshan

Re: Multiple columns into single column one after other

sas2srinivas — Wed, 01 Apr 2015 08:47:47 GMT

Thanks for your Quesry. I have executed the given code and got the below output

EMPName_Desi	EMPSAL
NameSureshDesiSE	10000
NameRameshDesiSSE	100000
NamenareshDesiSE	20302
NamemaheshDesiTL	203204

But I need out like below.Name and Desi in seperate lines. In one sigle cell first line Name , second line <Name>, third line "DESI" and fourth line <DESI>.

EMPName_Desi	EMPSAL
Name Suresh Desi SE	10000
Name Ramesh Desi SSE	100000
Name naresh Desi SE	20302
Name mahesh Desi TL	203204

Thanks

Sreenivas M

Re: Multiple columns into single column one after other

Ksharp — Wed, 01 Apr 2015 13:05:26 GMT

Did you open file c:\temp\x.xls ?

Re: Multiple columns into single column one after other

Kurt_Bremser — Wed, 01 Apr 2015 13:22:37 GMT

data _null_;

set have;

file "excelout.csv";

if _n_ = 1 then put "EMPName_Desi;EMPSAL";

put "Name;"@;

put EMPSAL;

put EMPName;

put "Desi";

put EMPDesi;

run;

Then open excelout.csv with Excel.

Re: Multiple columns into single column one after other

sas2srinivas — Fri, 03 Apr 2015 06:57:23 GMT

Thanks for code.

When I excute the above code the output is saving in multiple rows. I want one sigle rows like below.

Name Suresh Desi SE	10000
Name Ramesh Desi SSE	100000
Name Naresh Desi SE	20302
Name Mahesh Desi TL	203204

Re: Multiple columns into single column one after other

Patrick — Fri, 03 Apr 2015 08:02:40 GMT

If I execute 's code both with SAS EG and PC SAS then it does exactly what you've asked for (except when executing the code with PC SAS using DBCS - then the generated .xls is invalid and I can't open it).

Thanks

Patrick

Re: Multiple columns into single column one after other

Kurt_Bremser — Fri, 03 Apr 2015 08:03:14 GMT

Oh, I see. Looks different in the notification mail than on the website here.

Then I suggest to use Xia's code, with the only change in the filename extension from xls to xml, because Excel might complain about the file format when opening.