Order of fields in Excel output

gopilth — Fri, 29 Nov 2013 02:52:33 GMT

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;

test2	test1
1	2
1	2
1	2
1	2

Re: Order of fields in Excel output

Cynthia_sas — Fri, 29 Nov 2013 19:00:39 GMT

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 3

3 1

3 2

;

run;

** make fake character data;

data example_char;

infile datalines;

input test1 $ test2 $;

return;

datalines;

2 1

2 3

3 1

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;

Re: Order of fields in Excel output

gopilth — Sat, 30 Nov 2013 01:26:40 GMT

Thank you

topic Order of fields in Excel output in ODS and Base Reporting

Order of fields in Excel output

Re: Order of fields in Excel output

Re: Order of fields in Excel output