topic export to excel by file in SAS Procedures

export to excel by file

Augusto — Tue, 14 Feb 2012 18:52:07 GMT

Hi everyone

I am exporting a data set to excel using this data step

data _null_;
set ERS_CMP.CMP_ERS_MQ12 (obs = 100);
file "/CEGSAS/ERS/MQ12_TESTE.xls";
put (_all_) (:);
run;

The question is:

Is it a way to export to excel when you don't have the excel engine, or do i really need to have a excel engine

to export datas to excel?

I opened the MQ12_TESTE.xls but the values looks like as txt file.

Thanks

export to excel by file

art297 — Tue, 14 Feb 2012 18:56:13 GMT

I happen to prefer using the excel engine. However, you may be able to accomplish what you need by using the ExcelXP tagset. Take a look at: http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

export to excel by file

Augusto — Tue, 14 Feb 2012 19:01:06 GMT

Mr. art

To do exports to excel do I need to have SAS/ACCESS?

export to excel by file

SASKiwi — Tue, 14 Feb 2012 19:01:27 GMT

Try changing the file extension from XLS to CSV. That way when you double click on the file in Windows Explorer Excel should recognise it as a delimited file.

export to excel by file

art297 — Tue, 14 Feb 2012 19:02:01 GMT

Not with the tagsets

export to excel by file

Augusto — Tue, 14 Feb 2012 20:15:15 GMT

thanks art. you helped me again.

export to excel by file

Augusto — Tue, 20 Mar 2012 20:17:02 GMT

Hi Mister Art. I have a question regarding to tagsets.excelxp. When i run the second code below, the first sheet1 doesn't appear in the want.xls file, I mean is overwrited by the second execution. Is the a way to insert datas into many sheets using tagsets.excelxp? I do need to have more than one ods tagsets command because each one is belongs to its proccess.

ods tagsets.excelxp file = "c:\want.xls" options (sheet_name = 'sheet1');

proc print data = have1 noobs;

run;

ods tagsets.excelxp close;

ods tagesets.excelxp file = "c:\want.xls" options (sheet_name = 'sheet2');

proc print data = have2 noobs;

run;

ods tagsets.excelxp close;

export to excel by file

art297 — Tue, 20 Mar 2012 20:24:33 GMT

Augusto: try it after commenting out the first close statement. i.e.:

ods tagsets.excelxp file = "c:\art\want.xls" options (sheet_name = 'sheet1');

proc print data = sashelp.class noobs;

run;

*ods tagsets.excelxp close;

ods tagesets.excelxp file = "c:\art\want.xls" options (sheet_name = 'sheet2');

proc print data = sashelp.class noobs;

run;

ods tagsets.excelxp close;

BTW, It is either Dr. Art or just Art. "Mister" makes me feel to old

export to excel by file

Augusto — Tue, 20 Mar 2012 21:08:41 GMT

Hi Dr. Art.

Well, I guees..I am in trouble, because actually I have these codes separated each one in its process. not like I've shown here. I have one process called export_data1.sas and other export_data2.sas that must be processed separated and the results are exported to the same excel in separated sheets.

export to excel by file

art297 — Tue, 20 Mar 2012 21:23:07 GMT

Why not just save the resulting file from the first process as a permanent file then, when you run the second process, print both files?

export to excel by file

Augusto — Tue, 20 Mar 2012 21:38:16 GMT

I will do this. Is the only way.

Here in Brazil we usually call Mister those we think that have the power to know.

Thanks Art