topic Re: ODS Tagset excel - header in SAS Programming

ODS Tagset excel - header

Reddi — Wed, 08 Mar 2017 10:44:56 GMT

Hi ,

I'm trying the below program and need to show titles and footer in the exported excel file , may be like run date and some comments.

ods tagsets.csv file="%SYSFUNC(PATHNAME(WORK))/Motor_Daimler_&sysdate..csv" RS=NONE options(PREPEND_EQUALS="yes" QUOTE_BY_TYPE="yes" );

proc print data = Final_SUMMARY_12MTD label noobs;

run;

ods _all_ close;

Can someone amend the above program.

Thanks in advanceeee

Re: ODS Tagset excel - header

RW9 — Wed, 08 Mar 2017 10:49:56 GMT

CSV = Comma Separated Variable file - which is a more or less standard file format which has row 1 as column headings, and then the data on the following lines separated by commas. It does not support titles/footnotes or anything else.

What I assume your doing is wanting to open the data in Excel and see some Excel specific parts like titles/footnotes. If so then switch over to tagsets.excelxp and embed the titles - note that this is an output review file, not great for data transfer - but then CSV is ideal for data transfer and not good for output review so choose what the purpose is.

ods tagsets.excelxp file="...\abc.xml" options(embed_titles='yes');
proc print...;
run;
ods tagsets.excelxp close;

Re: ODS Tagset excel - header

Reddi — Wed, 08 Mar 2017 12:03:21 GMT

Hi @RW9,

cant we export it as a excel file ? also can you amend the program below including Title and footnote statement. I tried it with .xml format itself but no use.

Thanks

Re: ODS Tagset excel - header

RW9 — Wed, 08 Mar 2017 12:42:38 GMT

Tagsets.excelxp generates XML file which Excel can read just fine (you may need to assign XML as Open With Excel - you could call the file xlsx to get this automatically, but its not advised as its not really an Excel file). This tagset has all the necesary parts to make the output look nice - i.e. for review. As above, you haven't stated what the file is to be used for, either a report for review, or data transfer.

ods tagsets.excelxp file="...\abc.xml" options(embed_titles='yes');
title "Hello World";
proc print...;
run;
ods tagsets.excelxp close;

If you want CSV, then no titles. If you want pure XLSX file, then you can use libname excel, however again, you wont get titles/footnotes.

Re: ODS Tagset excel - header

Reddi — Wed, 08 Mar 2017 13:17:08 GMT

Hi @RW9,

still i'm not able to get the required.

My coding is below.

ods tagsets.excelxp file="%SYSFUNC(PATHNAME(WORK))/Motor_Daimler_&sysdate..xml" RS=NONE style=Journal

options(PREPEND_EQUALS="yes" QUOTE_BY_TYPE="yes") options(embed_titles='yes');

title "Sample1";

proc print data = Final_SUMMARY_12MTD label noobs;

run;

ods tagsets.excelxp close;

filename outbox email;

If you can please amend the program.

Re: ODS Tagset excel - header

RW9 — Wed, 08 Mar 2017 13:50:05 GMT

This should work, though I can't check at the moment:

ods tagsets.excelxp file="%sysfunc(pathname(work))/motor_daimler_&sysdate..xml" style=journal
  options(embed_titles='yes');

title "sample1";
proc print data = final_summary_12mtd label noobs;
run;

ods tagsets.excelxp close;

Oh, its probably not a good idea to write the file to work as that is a temporary area.