The SAS Output Delivery System and reporting techniques

ODS CSV EXCEL

Reply
New Contributor
Posts: 2

ODS CSV EXCEL

By running the following statemant to export the results of a "proc tabulate" to EXCEL, SAS did a perfect job, except that I miss the the class variable (ir_Nr_GTAP) at the top of each tables (see above):

ods xml file="G:\GTAP\SAS_OUTPUT_XLS\DK_NAMA_EU_BT.csv" type=csvall;
PROC tabulate DATA = DK.SZ_EU_SG_IrG_ErG;
CLASS sct_Nr_GTAP ir_Nr_GTAP er_Nr_GTAP;
VAR BT__;
TABLE ir_Nr_GTAP, sct_Nr_GTAP, er_Nr_GTAP *(BT__ * mean * F=10.6 )
/ PRINTMISS RTS = 14;
RUN; quit;
ods xml close;


All the tables look like this, without reporting the ir_Nr_GTAP variable at teh top of each table:

RESULT in EXCEL :

er_Nr_GTAP
1 2 3
BT__ BT__ BT__
Mean Mean Mean
sct_nr_GTAP . 0.000000 20.000.000
1
2 . 0.000000 20.000.000
3 . 2.296.143 43.079.649
4 . 1.368.650 50.468.266
5 . 0.000000 45.000.000
6 . . .
7 . . 11.000.000


er_Nr_GTAP
1 2 3 4
BT__ BT__ BT__ BT__
Mean Mean Mean Mean
sct_nr_GTAP . . . .
1
2 . . . .
3 1.000.000 . . 91.200.000
4 0.787551 . 56.068.537 11.601.399
5 1.000.000 . . 5.000.000
6 . . . .
7 . . . .


Has anybody any hint to solve this Problem ?

An other trial was: I tried to export the tables by HTML, but the file size is to big an EXCEL can't open the files (ERROR no response of EXCEL)

thanks
SAS Employee
Posts: 174

Re: ODS CSV EXCEL

Have you tried using the ExcelXP tagset?

[pre]
ods tagsets.excelxp file="yourfile.xls";
proc tabulate data=....;
run;
ods tagsets.excelxp close;
[/pre]

ExcelXP is generally the best way to put SAS tables into Excel using ODS.

-- David Kelley, SAS
SAS Super FREQ
Posts: 8,868

Re: ODS CSV EXCEL

Posted in reply to David_SAS
If, however, you WANT to get a CSV file, then you -can- get your page dimension into the CSV file as long as you do this:

1) Keep using the CSVALL tagset -- I prefer using the ODS MARKUP statement directly or ODS CSVALL instead of the ODS XML statement for this.
[pre]
ODS CSVALL ...; ODS MARKUP TAGSET=CSVALL ...;
ODS CSVALL CLOSE; ODS MARKUP CLOSE;
[/pre]
(depending on your version of SAS -- I believe that CSVALL was not an invocable alias until SAS 8.2)

AND
2) Use box=_page_ on your TABLE Statement to force the text INSIDE the Tabulate table area. You may get a message in LISTING that the page text is too long. This will NOT bother HTML or CSVALL, so you can ignore this NOTE.

[pre]
ods markup tagset=csvall file='newtable.csv;

**PROC TABULATE code but change your table statement;
table .....
/ box=_page_;
ods markup close;
[/pre]

and when you open the CSV file with EXCEL, you should see the page text on the same line as the first row of headers (probably in column A).

cynthia
New Contributor
Posts: 2

Re: ODS CSV EXCEL

Dear David, dear Cynthia,

thanks for your immediate help/hints.

I got an error message. Both solutions are fousing on SAS V9, therefore firstly I have to install the new version, because I'm still using V8.01, but nevertheless it is a great step forward for me.

cheers rainer
SAS Super FREQ
Posts: 8,868

Re: ODS CSV EXCEL

Hi!
CSVALL was actually available in SAS 8.2. But ExcelXP is definitely SAS 9. I am surprised that the ODS XML syntax worked for you in SAS 8.01 -- but perhaps it was there in a rudimentary form. Honestly, I don't remember.

An older, somewhat different method of creating CSV and HTML files did exist. You may not have heard about them. They were called the "HTML Formatting Tools Macros" -- they included ways to get tabulate output into HTML (which you can open with Excel) or SAS datasets into CSV form (which you can also open with Excel).

We used to teach these macros in SAS 6, so they may be out there in your installation. The 2 macros of interest to you might be: %tab2htm (sends proc tabulate to a minimal HTML file, which opens in Excel 97 and higher) and
%ds2csv (sends a SAS dataset to CSV, which opens in Excel)


They are documented on the SAS/IntrNet page, (but they have a batch mode, too.) The link where you can find some examples and explanations for the options is:
http://support.sas.com/rnd/web/intrnet/format/index.html

But, here's a quick example you can try. (When you go to use the page dimension, I think you will still have to use box=_page_.) At any rate, it's not a CSV file, if you use %tab2htm, but it is pretty sparse in terms of HTML. So it may be smaller in size that what you're currently creating. After you run these macros you will NOT see anything in the SAS Results Window -- you have to use Windows Explorer or IE or Excel or Notepad to navigate to the files and open them.
cynthia
[pre]
**** the code;


filename outfile 'c:\temp\class.csv';
%ds2csv(csvfref=outfile,data=sashelp.class,
runmode=b);


options nodate nonumber ls=190 ps=100 nocenter;
filename tabfile 'c:\temp\classtab.xls';
%tab2htm(capture=on,
runmode=b);

proc tabulate data=sashelp.shoes;
Title "using Tab2HTM Macro";
var sales;
class region;
table region all,
sales*(min mean max);
run;


%tab2htm(capture=off,
runmode=b,
openmode=replace,
htmlfref=tabfile,
brtitle=Tab2Htm,
center=N);
[/pre]
Ask a Question
Discussion stats
  • 4 replies
  • 189 views
  • 0 likes
  • 3 in conversation