BookmarkSubscribeRSS Feed
gopilth
Calcite | Level 5

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
2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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;

gopilth
Calcite | Level 5

Thank you

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 1023 views
  • 0 likes
  • 2 in conversation