BookmarkSubscribeRSS Feed
sas2srinivas
Calcite | Level 5

    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

8 REPLIES 8
jwillis
Quartz | Level 8

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
Super User
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
Calcite | Level 5

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

Ksharp
Super User

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

Patrick
Opal | Level 21

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
Super User

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
Calcite | Level 5

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 983 views
  • 0 likes
  • 5 in conversation