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
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
Thanks for the reply Amit, however the output to Excel is still the same.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.