Proc Template Histogram Loop

[mrktmixologist]

Hi,

I am working on a loop that will create histograms for each variable in a data set and then export them into a file in an excel file for each histogram. The code is creating the histograms, however I cannot export them as excel files (only in pdf). The error code I get after running the code is: ERROR: The HTML destination is not active; no select/exclude lists are available. I need to put each histogram in a power point presentation, which is why I was hoping to export them as excel files. Near the bottom in bold red font is the code that causing the issue. Any help would be much appreciated. Thank You.

Cheers,

Chris

/*SET FILE DIRECTORIES*/

%let dir = C:\Users\Chris\Dropbox\Progressive\MMM_Auto1B\Deliverable\Histograms;

%let data = &dir\Data;

%let readin = &data\ReadIn;

/*IMPORT DATA*/

proc import datafile = "&readin\Histogram_States_V2.csv"

out = states dbms = csv replace;

guessingrows = 1000;run;

proc import datafile = "&readin\Histogram_Mean_1A_V2.csv"

out = means dbms = csv replace;run;

/*NUMBER EACH VARIABLE AND CREATE MACRO VARIABLE FOR VARIABLE NUMBER*/

data coeffs_means;

  keep Var Auto1B_Var_Name Varno Auto1A_Coeff Mean;

  set means;

  retain Varno 0;

  Varno + 1;

  call symput('varno', left(trim(varno)));

run;

/*HISTOGRAM MACRO*/

%macro histo;

%do iterate = 1 %to &varno;

  data _null_;

  set coeffs_means (where = (Varno = &iterate));

  call symput('coeff', auto1a_coeff);

  call symput('mean', mean);

  call symput('var', var);

  call symput ('var2', Auto1B_Var_Name);

  run;

%put &coeff;

%put &mean;

%put &var2;

%put &var;

proc template;

define statgraph &var2;

dynamic &var;

begingraph;

   layout lattice / rowdatarange=data columndatarange=data rowgutter=10 columngutter=10;

      layout overlay;

         histogram &var2 / name="&var2" legendlabel='Auto1B' binaxis=false fillattrs=(color=CX8EA026 );

         referenceline x=&coeff / name='Auto1A Coeff' xaxis=X curvelabelposition=max lineattrs=(color=CX0000FF thickness=3 );

         referenceline x=&mean / name='Mean' xaxis=X curvelabelposition=max lineattrs=(color=CXFF0000 thickness=3 );

         discretelegend 'Auto1B' 'Auto1A Coeff' 'Mean' /

  opaque=false border=true halign=right valign=top displayclipped=true across=1 order=rowmajor location=inside;

      endlayout;

   endlayout;

endgraph;

end;

run;

ods html select "C:\Users\Chris\Dropbox\Progressive\MMM_Auto1B\Deliverable\Histograms\Graphs\&var2._graph.xls";

proc sgrender data = work.states template = &var2;

dynamic &var2 = "&var2";

run;

ods html close;

%end;

%mend histo;

/*RUN MACRO*/

%histo;

[ballardw]

It appears that you are running by default with the html desitination turned off.

Try adding:

Ods Html path="C:\folder of choice" ;

or a more complete path for your destination files

before the ods htm select;

I suspect that you may have gotten one output then none as your CLOSED the HTML destination on the first loop and the loop doesn't reopen HTML.

OR move the HTML close out of the macro and use:

Ods Html path="C:\folder of choice" ;

%histo;

Ods html close;