Help using Base SAS procedures

How to export the results of PROC LOGISTIC to excel file

Reply
Contributor
Posts: 32

How to export the results of PROC LOGISTIC to excel file

Hi all,

 

I am using PROC LOGISTIC on multiple datasets , for this I am using macro. Is there a way so I can export the results to excel sheet for each dataset.

I tried using ODS TAGSETS.Excelxp, but not working:

 

%macro build(in_ds);

ods graphics on;

proc logistic data=&in_ds. ALPHA=0.05  outmodel=DS_MDL;

class var1 var2 var3;

model y=x / options;

score data=&in_ds. out=ds_scr;

run;

ods graphics off;

%mend;

 

ODS TAGSETS.EXCELXP
file='D:worksas9regression.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

 

%build(dataset1);

%build(dataset2);

%build(dataset3);

 

ODS TAGSETS.EXCELXP close;

 

I want to create separate reports for 3 datasets, the report name can be passed as a parameter to macro.

Please suggest.

Thanks

Super User
Super User
Posts: 9,599

Re: How to export the results of PROC LOGISTIC to excel file

Posted in reply to UshaLatha

Use the dark and forgotten art of by group processing. 

ods tagsets.excelxp file='d:/worksas9regression.xml' style=minimal options(orientation='landscape' fittopage='yes' pages_fitwidth='1' pages_fitheight='100');

data have;
  length ds_name $20;
  set dataset1 dataset2 dataset3 indsname=tmp;
  ds_name=tmp;
run;

ods graphics on;

proc logistic data=have alpha=0.05 outtmodel=ds_mdl;
  by ds_name;
  class var1 var2 var3;
  model y=x / options;
  score data=have;
run;

ods graphics off;

ods tagsets.excelxp close;

Do note I have corrected the various typos, the random casing, lack of indents etc.  I also called the filename xml which is what you are actually creating here by use of a tagset.  Also note in the tagset options you may need to set sheet_inteval='bygroup'.  As I have nothing to test this on it isn't tested.

Contributor
Posts: 32

Re: How to export the results of PROC LOGISTIC to excel file

hi,

The datasets here are all having different set of columns.

Is there any way to avoid data-set statement part..

Super User
Super User
Posts: 9,599

Re: How to export the results of PROC LOGISTIC to excel file

Posted in reply to UshaLatha

Pleas post some test data, and code which accurately reflect what you have, we can only go on what you post here.  Your error is to do with gpath:

https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_odsgraph_se...

 

If the datasets are different and the variables are different, how are you aiming to get the different variables into the macro code?  If the same variables are being used in the model each time, just keep them in the dataset.  If they are different variables then show how this will go into the macro.

Valued Guide
Posts: 580

Re: How to export the results of PROC LOGISTIC to excel file

Posted in reply to UshaLatha

Please explain what "not working" means. And: post code using either {i} or running-man icon.

 

There seems to be an error in the specification of the file that ods should generate.

Contributor
Posts: 32

Re: How to export the results of PROC LOGISTIC to excel file

Posted in reply to andreas_lds

 

%macro build_model(in_dset);
     ods graphics on;
     proc logistic data=&in_dset. ALPHA=0.05  /*&model_plots.*/ outmodel=ds_MDL;
     class &list_class_vars.;
     model DEFAULT_FLAG(EVENT='1')=&list_loan_vars. &selected_macroeconomic_vars.  / &model_opts.;
     score data=&in_dset. out=ds_SCR;
     run;
     ods graphics off;
%mend build_model;



%let plot_path = /home/../../../reports;
ods tagsets.ExcelXP file="&plot_path./report.xml" style=minimal options(orientation='landscape' FittoPage = 'yes' Pages_FitWidth ='1' Pages_FitHeight = '100');
%build_model(dataset1);
ods tagsets.ExcelXP close;


error message:
NOTE: Convergence criterion (GCONV=1E-8) satisfied in Step 17.
NOTE: Convergence criterion (GCONV=1E-8) satisfied in Step 18.
NOTE: Convergence criterion (GCONV=1E-8) satisfied in Step 19.
NOTE: Convergence criterion (GCONV=1E-8) satisfied in Step 20.
WARNING: GPATH or PATH is not a writable directory. It will be ignored.
ERROR: Cannot write image to . Please ensure that proper disk permissions are set.
ERROR: Cannot write image to . Please ensure that proper disk permissions are set.
NOTE: The SAS System stopped processing this step because of errors.

Please find my code and error messages.

 

Super User
Posts: 13,583

Re: How to export the results of PROC LOGISTIC to excel file

Posted in reply to UshaLatha

I don't believe the XML files that tagsets.excelxp generates can contain graphics. Either make sure that there are no graphics generated or use a different destination such as ODS RTF or ODS PDF.

Ask a Question
Discussion stats
  • 6 replies
  • 202 views
  • 1 like
  • 4 in conversation