The SAS Output Delivery System and reporting techniques

Writing proc summary to an excelfile

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Writing proc summary to an excelfile

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;


Accepted Solutions
Solution
‎07-02-2014 09:39 AM
SAS Super FREQ
Posts: 8,647

Re: Writing proc summary to an excelfile

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;

View solution in original post

Attachment

All Replies
Esteemed Advisor
Posts: 6,006

Re: Writing proc summary to an excelfile

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 33

Re: Writing proc summary to an excelfile

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;

Solution
‎07-02-2014 09:39 AM
SAS Super FREQ
Posts: 8,647

Re: Writing proc summary to an excelfile

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;

Attachment
Contributor
Posts: 33

Re: Writing proc summary to an excelfile

      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.

Esteemed Advisor
Esteemed Advisor
Posts: 6,729

Re: Writing proc summary to an excelfile

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.

Contributor
Posts: 33

Re: Writing proc summary to an excelfile

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

SAS Super FREQ
Posts: 8,647

Re: Writing proc summary to an excelfile

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

Post a Question
Discussion Stats
  • 7 replies
  • 1050 views
  • 6 likes
  • 4 in conversation