topic Re: How to export the results of PROC LOGISTIC to excel file in SAS Procedures

How to export the results of PROC LOGISTIC to excel file

UshaLatha — Wed, 13 Dec 2017 08:57:49 GMT

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

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

RW9 — Wed, 13 Dec 2017 09:22:39 GMT

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.

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

andreas_lds — Wed, 13 Dec 2017 09:26:34 GMT

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.

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

UshaLatha — Wed, 13 Dec 2017 10:50:20 GMT

%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.

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

UshaLatha — Wed, 13 Dec 2017 10:56:29 GMT

hi,

The datasets here are all having different set of columns.

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

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

RW9 — Wed, 13 Dec 2017 11:15:57 GMT

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_sect032.htm

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.

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

ballardw — Wed, 13 Dec 2017 20:43:53 GMT

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.