topic Re: Writing proc summary to an excelfile in ODS and Base Reporting

Writing proc summary to an excelfile

Kiteulf — Wed, 02 Jul 2014 12:35:18 GMT

I am trying to write an excel file the output of a summary.

What am I doing wrong?

ods tagsets.ExcelXP file='G:\Risk\Portefølje\Eirik W\Excel_output\test.xls' style=journal;

Proc Summary data=Direkte_LOAN6 nway missing;

VAR UTLAN;

Class PROD PERIODE;

Output out=Loan_portfolio (drop= _TYPE_) sum=;

Where PERIODE > '0811' AND ALDER_KTO = 1 /*AND NPL = 1*/;

run;

ods tagsets.ExcelXP close;

Re: Writing proc summary to an excelfile

Kurt_Bremser — Wed, 02 Jul 2014 12:38:36 GMT

By default, PROC SUMMARY does not produce print output. Either use PROC MEANS or specify the PRINT option on the PROC SUMMARY statment.

Re: Writing proc summary to an excelfile

Kiteulf — Wed, 02 Jul 2014 13:12:39 GMT

Even though I use print in teh statement it will not run

Proc Summary data=Direkte_LOAN6 nway missing print;

VAR UTLAN;

Class PROD PERIODE;

Output out=Loan_portfolio (drop= _TYPE_) sum=;

Where PERIODE > '0811' AND ALDER_KTO = 1 /*AND NPL = 1*/;

run;

Re: Writing proc summary to an excelfile

Cynthia_sas — Wed, 02 Jul 2014 13:39:09 GMT

Hi:

1) are there messages in the SAS log, if so, what are they

2) do you have your ODS destination statements in the code

3) when I run the attached code, I do get output. See attached.

4) Did you try PROC MEANS instead of PROC SUMMARY?

5) What do you want to see in Excel -- the results of the PROC SUMMARY or the print of the Loan_portfolio dataset? They are 2 different outputs, as you will see if you run the code below.

Since I cannot replicate your issue with my code, my suggestion is that you open a track with Tech Support for deeper investigation.

Cynthia

ods tagsets.excelxp file='c:\temp\prd_out.xml' style=sasweb;

Proc Summary data=sashelp.prdsale nway missing print;

VAR actual;

Class PRODTYPE country;

Output out=Loan_portfolio (drop= _TYPE_) sum=;

Where quarter=1 ;

run;

proc print data=loan_portfolio;

run;

ods tagsets.excelxp close;

Re: Writing proc summary to an excelfile

Kiteulf — Wed, 02 Jul 2014 14:23:41 GMT

ods tagsets.ExcelXP file='G:\Risk\Portefølje\Eirik W\Excel_output\test.xls' style=journal;

NOTE: Writing TAGSETS.EXCELXP Body file: G:\Risk\Portefølje\Eirik W\Excel_output\test.xls

ERROR: Physical file does not exist, G:\Risk\Portefølje\Eirik W\Excel_output\test.xls.

WARNING: No body file. TAGSETS.EXCELXP output will not be created.

Re: Writing proc summary to an excelfile

Kiteulf — Wed, 02 Jul 2014 14:25:29 GMT

I would like to write an excelfile of the Loan_portfolio dataset.

Re: Writing proc summary to an excelfile

RW9 — Wed, 02 Jul 2014 14:44:27 GMT

Does this directory exist on your (where SAS is located) machine?

G:\Risk\Portefølje\Eirik W\Excel_output\

The error is saying that it cannot find that path, or that you don't have write permission to that area to create the file.

Re: Writing proc summary to an excelfile

Cynthia_sas — Wed, 02 Jul 2014 15:27:25 GMT

Hi:

If that is the case, then you will need to use PROC PRINT or PROC REPORT on the Loan_portfolio dataset, as shown in my test code. However, you will have to resolve the issue of the correct path for your output results first. As RW9 explained, your ERROR message is happening because either 1) you do not have WRITE access to the physical path location in your FILE= option or 2) that is the wrong path name or 3) the path name or some folder in the path does not exist.

cynthia