topic Re: Column labels not exporting to Excel in ODS and Base Reporting

Column labels not exporting to Excel

FastEddie — Thu, 02 Jul 2015 13:28:33 GMT

Thanks in advance for your help. (Using SAS 9.2 )

I'm a fairly new user to SAS and I'm having issues with when exporting my data to excel, the dates which are in the iDATE column do not label in excel, instead the just have a header of _C2_ _C3__C4__C5_ etc.

My code is below:

data work.variance (keep = Variance Cust_ID iDATE);
       set CUSTIM.AREC;
       if Variance < 250;
 run;

proc report data=work.variance out = newdata;
      column Cust_ID iDATE Variance;
       define Cust_ID / group;
       define iDATE / across;
       define Variance / sum;
run;

Below is what excel shows

Thanks

FastEddie

Re: Column labels not exporting to Excel

AmitRathore — Thu, 02 Jul 2015 14:44:53 GMT

Hey FastEddie,

I do not why you want to extract the excel output but to do Excel Tagsets is very good options. Please check if below program helps you :

ods listing close;

footnote;

title ' ';

ods tagsets.excelxp file="C:\temp\test.xls"

style=sasweb

options(sheet_interval='none'

embedded_titles='yes'

sheet_name='test'

default_column_width="20, 20, 20, 30" width_fudge='0.75'

skip_space='0,0,0,0,1'

autofilter = 'all'

frozen_headers="1"

);

proc report data=work.variance out = newdata;

column Cust_ID iDATE Variance;

define Cust_ID / group;

define iDATE / across;

define Variance / sum;

run;

ods tagsets.excelxp close;

ods listing;

quit;

Br, Amit

Re: Column labels not exporting to Excel

FastEddie — Thu, 02 Jul 2015 14:56:58 GMT

Thanks for the reply Amit, however the output to Excel is still the same.

Re: Column labels not exporting to Excel

RW9 — Thu, 02 Jul 2015 16:00:26 GMT

What does your dataset work.variance look like? What you have in your output is a default, so you haven't labelled the columns anywhere or provided any data with which to label them.

Re: Column labels not exporting to Excel

Cynthia_sas — Fri, 03 Jul 2015 02:01:13 GMT

Hi:

PROC REPORT is working as DESIGNED. When you use an ACROSS variable, PROC REPORT makes a column for every UNIQUE value of the variable. One of the "pre-processing" steps that PROC REPORT does is name the variables _C2_, _C3_, etc, as described in this paper.

http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

So, when you make a dataset (using OUT=) and then export the dataset, the columns get the "new" absolute column names. They do NOT keep their original values. You would be better off just sending the PROC REPORT output straight to Excel. For example, if you run this code, both outputs show the date values for me (you will need 9.4 to run the ODS EXCEL statement and get output -- so I have commented it out for those who might not have 9.4).

Cynthia

ods tagsets.excelxp file='c:\temp\across.xml' style=sasweb;

**ods excel file='c:\temp\across.xlsx' style=sasweb;

proc report data=sashelp.prdsale nowd;

where year = 1993;

column country actual,month;

define country / group;

define month / across ' ' f=mmddyyd10. order=data;

define actual / sum 'Actual Sales';

run;

ods _all_ close;