Create Excel Output File from SAS table 1

[JM3482]

I need to take a table that I have created in SAS and have it automatically output in excel format. I have already tried this with the ods html file = filename..... and changing it to an .xls file extension, but I need more than just the "Results Viewer". I need for the file to automatically output to excel so it can then be saved in a specific location for the end user to access. I don't want to set up templates if I don't have to.

 

[jymm]

I have not had to do this for some time (converted the exports to SQL or VB since I had more control) --- BUT if you can get by with your users having a tab delimited file do something like this

proc export data=Work.Accounts Outfile='h:\Accounting\Accountlist.txt' replace;

This should do what you want

They also have updated some of the DDE examples on the SAS examples - check this one out - I would never have converted last year if I had this example

http://ftp.sas.com/techsup/download/sample/datastep/openxl.html

 

[jymm]

forgot two things about the DDE stuff --- SAS has/had a REALLY bad problem with excel files where the sheet names were really long and also had a rough time importing if data was numeric and you really wanted to keep it as text (the import nightmare is slowly coming back to me)...

 

[karlomutschler]

Hi
this is an script that I use for exporting data from a SAS table to an excel file:

PROC EXPORT DATA= WORK.tablename
OUTFILE= "Path\filename.xls"
DBMS=EXCEL2000;
RUN;

HiH
Kind regards
Karlo

meistertools@gmx.net

 

[ChrisW75]

Have you tried using HTMLCSS rather than HTML? IT produces much better results. I've been using Proc Report to write out Excel files for a while and have finally got a report that looks good coming out. IF this sounds like it's worth your while, post back and I'll dump all the code I've used to clean up the report.
The problem with loading reports into Excel is that Excel frequently ignores the styles, I've found a way around it using a little post processing macro.

 

[JM3482]

This sounds very interesting. Please do supply me with code. I will try it. Possibly this will work for my new problem. I have now had to create a template and place the data from my SAS table on this template. That works, but they want me to replace the excel spreadsheet each time I run the report with the new data and remove the old. I may need to look at proc report to get my final step of replacing the excel spreadsheet each day

Any help would be appreciated. I am just learning to write SAS coding and find myself struggling with only a beginners class behind my belt.

 

[ChrisW75]

OK here goes. I'll post the macro code in a second post and the data needed for the macro in a third (it's a pain to set up, but it only needs to be done once!).
The HTML class DP is one of my own style definitions, not a stand one.

/*Create your report using Proc Report with an ODS HTMLCSS output */

ods htmlcss file="&output";

proc report data=work.whatever;
columns name dss_amount.....;

define name /display ;
define dss_amount /display 'DSS Payment*Amount'
format=10.2
style(column)= HTMLCLASS="DP"};

/* Use compute rather than title to avoid nasty
blank lines between title lines */

compute before _page_ /
style={htmlclass="systemtitle"};
line "&title1";
line "Current as at : &snapshdt";
line " ";
endcomp;
run;

ods htmlcss close;

/* Macro to post process the html file and add in the definitions */

%css(&output);

 

[ChrisW75]

Macro to post process the HTML file.

* Macro css(filename) takes an html output file and adds in the style definition *;
* to the top of the html file in the style section. *;


%macro css(htmfile);

filename infl "&htmfile";

/* Read in style definitions */
data styles;
infile 'css_style_def.txt' length=linelen;

length text $500;

input @1 text $varying500. linelen;
run;

/* Read in the HTMLCSS file and split it in two */
data part1
part2;
infile infl recfm=v lrecl=500 length=linelen;

length text $500;

input @1 text $varying500. linelen;

retain flaga 0;

if flaga=2 then output part2;

if flaga=0 then do;
if text='<style>' then do;
flaga=1;
end;
output part1;
end;

if flaga=1 and text='-->' then do;
output part1;
flaga=2;
end;

else if flaga=1 then output part1;


run;

/* bring 2 parts back together again with new style defs inside */
data all;
set part1
styles
part2;

file infl recfm=v lrecl=500;
put @1 text ;

run;

%mend css;

 

[ChrisW75]

Final stage, this needs to be put into the file you point to in %css. System Title is class given to the SAS titles, System Footer to the footnotes, Data is given to the values in the table, header for the column headers, and rowheader to the rowheaders. Tweak these to get your standard tmeplate set up.


.SystemTitle
{
font-family: Arial, Helvetica, Helv;
font-size: 14pt;
font-weight: bold;
font-style: italic;
color: #000000;
background-color: #FFFFFF;
text-align: center;
}
.SystemFooter
{
font-family: Arial, Helvetica, Helv;
font-size: 9pt;
font-weight: bold;
font-style: normal;
color: #000000;
background-color: #FFFFFF;
text-align: center;
}
.Body
{
font-family: Arial, Helvetica, Helv;
font-size: 9pt;
font-weight: normal;
font-style: normal;
color: #000000;
background-color: #FFFFFF;
margin-left: 8px;
margin-right: 8px;
}
.Table
{
font-family: Arial, Helvetica, Helv;
font-size: 9pt;
font-weight: normal;
font-style: normal;
color: #000000;
background-color: #FFFFFF;
border: 1pt outset ;
}
.Header
{
font-family: Arial, Helvetica, Helv;
font-size: 12pt;
font-weight: bold;
font-style: normal;
color: #000000;
background-color: #F8B500;
}
.RowHeader
{
font-family: Arial, Helvetica, Helv;
font-size: 12pt;
font-weight: bold;
font-style: normal;
color: #000000;
background-color: #F8B500;
}
.Data
{
font-family: Arial, Helvetica, Helv;
font-size: 11pt;
font-weight: normal;
font-style: normal;
color: #000000;
background-color: #FFFFFF;
mso-number-format:&quot;\@&quot;;
}
.DP
{
font-family: Arial, Helvetica, Helv;
font-size: 11pt;
font-weight: normal;
font-style: normal;
color: #000000;
background-color: #FFFFFF;
MSO-NUMBER-FORMAT:&quot;0\.00&quot;;
}

 

[ChrisW75]

Yes, I know, it's a bit of a nightmare to set up. I've actually done alot of work outputting reports to Excel, and all the standard ways are a bit hit and miss I've found. Some will work with Report, but not Tabulate etc. Excel frequently seems to ignore tags, unless you set up an external CSS stylesheet to reference, but then the output file is not self contained making distribution a pain.

One thing to note here is that the output file is an HTML file with an embedded style sheet, if you give it a .xls file extension, Excel will happily read it in and process it using the styles defined and works nicely, however, there is a processing overhead. If you are writing a report significantly more than 1000 rows, it will start to take a noticeable amount of time to open it. 4000 rows takes over a minute to open on my machine, which is a pretty good spec. Bear this in mind if you are dumping large amounts of data out.

The reason for my long winded way of doing it was to get the corporate branding colours into the headers etc. Hope this helps.

 

[JM3482]

Thanks, that is a bunch of code. I will see what I can do with it.