The SAS Output Delivery System and reporting techniques

Order of fields in Excel output

Reply
Occasional Contributor
Posts: 10

Order of fields in Excel output

Hi,

I am trying to generate an excel output using proc report and the order of columns in the output spread sheet is not matching the order that I have mentioned in code and I have defined sheet label and column names that should be on excel sheet in code but output shows the ones from data sets and not defined values in code. Can someone please suggest what should be changed in below code that I am using. Thanks in advance.

ods op.ExcelXP

options ( sheet_name="T01"

default_column_width ="10,10,10,10,10,10,10,10" );

proc report data = outp.example_new

 

columns test1 test2;

define test1 / "T1";

define test2 / "T2";

run;

output that I get is as follows;

test2test1
12
12
12
12
SAS Super FREQ
Posts: 8,816

Re: Order of fields in Excel output

Hi, I cannot replicate your results using TAGSETS.EXCELXP. I do not know what ODS OP.EXCELXP means as a destination -- is this a tagset template that you have written yourself?

Also, you do not show all of your code or your data. But when I made some FAKE data I actually made 2 tests -- one where TEST1 and TEST2 were both numeric variables and the other where TEST1 and TEST2 were both character variables. For the first test (both numeric variables), PROC REPORT gave me an overall summary report, because the usage of TEST1 and TEST2 would have been ANALYSIS with the SUM statistic. However, when TEST1 and TEST2 were both character variables, then I saw the correct order to the variables in my output and I had a row in my output for each row in the data.

I would recommend that you work with Tech Support on this, because I do not understand how you got your posted output from your posted code. It has to be something with your data or there's more to your code and/or something with the OP.EXCELXP destination that you use.

Cynthia

** make fake numeric data;

data example_num;

  infile datalines;

  input test1 test2;

return;

datalines;

2 1

2 1

2 1

2 3

3 1

3 1

3 2

3 2

;

run;

** make fake character data;

data example_char;

  infile datalines;

  input test1 $ test2 $;

return;

datalines;

2 1

2 1

2 1

2 3

3 1

3 1

3 2

3 2

;

run;

  

ods tagsets.ExcelXP

   file='c:\temp\show_examp.xml' style=sasweb

   options ( sheet_label="T01_numeric"

             default_column_width ="10,10,10,10,10,10,10,10" );

proc report data = example_num nowd;

title '1) posted code with NUMERIC variables and their default usage';

columns test1 test2;

define test1 / "T1";

define test2 / "T2";

run;

 

ods tagsets.excelxp options(sheet_label="T01_character");

proc report data = example_char nowd;

title '2) posted code with CHARACTER variables and their default usage';

columns test1 test2;

define test1 / "T1";

define test2 / "T2";

run;

ods _all_ close;

Occasional Contributor
Posts: 10

Re: Order of fields in Excel output

Thank you

Ask a Question
Discussion stats
  • 2 replies
  • 421 views
  • 0 likes
  • 2 in conversation