Multiple columns into single column one after other

[sas2srinivas]

    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

[jwillis]

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;

     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;

[Ksharp]

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

[sas2srinivas]

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

[Ksharp]

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

[Patrick]

Hi

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

[Kurt_Bremser]

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.

[sas2srinivas]

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

[Kurt_Bremser]

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.