Help using Base SAS procedures

Multiple columns into single column one after other

Reply
New Contributor
Posts: 3

Multiple columns into single column one after other

    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_DesiEMPSAL

Name

Suresh

Desi

SE 

10000

Name

Ramesh    

Desi

SSE

100000

Name

Naresh

Desi

SE 

20302

Name

Mahesh

Desi

TL         

203204

Thanks

Sreenivas

Regular Contributor
Posts: 217

Re: Multiple columns into single column one after other

Posted in reply to sas2srinivas

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;

Super User
Posts: 10,023

Re: Multiple columns into single column one after other

Posted in reply to sas2srinivas
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

New Contributor
Posts: 3

Re: Multiple columns into single column one after other

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

EMPName_DesiEMPSAL
NameSureshDesiSE10000
NameRameshDesiSSE100000
NamenareshDesiSE20302
NamemaheshDesiTL203204

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_DesiEMPSAL

Name

Suresh

Desi

SE

10000

Name

Ramesh

Desi

SSE

100000

Name

naresh

Desi

SE

20302

Name

mahesh

Desi

TL

203204

Thanks

Sreenivas M

Super User
Posts: 10,023

Re: Multiple columns into single column one after other

Posted in reply to sas2srinivas

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

Respected Advisor
Posts: 4,173

Re: Multiple columns into single column one after other

Posted in reply to sas2srinivas

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

Super User
Posts: 7,771

Re: Multiple columns into single column one after other

Posted in reply to sas2srinivas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Multiple columns into single column one after other

Posted in reply to KurtBremser

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
Super User
Posts: 7,771

Re: Multiple columns into single column one after other

Posted in reply to sas2srinivas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 8 replies
  • 363 views
  • 0 likes
  • 5 in conversation