SAS macro and proc univariate, change endpoints for each group

[statz]

Hi, 

I have 2 variables in my dataset - y and group. For each group, I want to create a histogram with endpoints calculated within the data for each group. My data could change, therefore,  the number of observations and number of groups may change for every diffrent data. For each group, i calculated the bandwidth(bw), lower limit (lw) and upper limit (up). I have to use these values to specify the endpoints for each histogram. But I keep on getting errors. Do you have any suggestion to improve and correct my current code? I want to output only the histogram for each group with specific endpoints. Thank you. 

data test;
input y group @@;
datalines;
35 1 46 2
32 1 43 2
38 1 46 2
32 1 46 2
32 1 40 2
38 1 50 2
36 1 49 2
33 1 40 3
37 1 42 3
33 1 40 3
49 2 42 3
45 2 43 3
49 2 45 3
41 2 45 3
47 2 49 3
48 2 40 3
45 2 58 3
47 3 59 3
56 4 56 3
52 4 51 4
60 4 57 4
60 4 53 4
50 4 50 4
56 4 52 4
53 4 51 4
50 4 52 4
;
run;

%macro histo;

proc sort data=test out=test1;
by group;
run;

proc means data=test1 min max range;
by group;
var y;
output out=dishist2(drop=_TYPE_ _FREQ_) min=min max=max range=range;
run;

data dishist2;
set dishist2;
bw=(max-min)/10;
lw=floor(min/bw)*bw-(3*bw);
up=floor(((max/bw)+1)*bw)+(3*bw);
run;

proc sql noprint ;
select bw ,lw, up
into :bw1 separated by ' ',
:lw1 separated by ' ',
:up1 separated by ' '
from dishist2;
quit;

%let cnt=&sqlobs;

%do i=1 %to &cnt;
%let lww= %scan(&lw1,&i);
%let upp= %scan(&up1,&i);
%let bww= %scan(&bw1,&i);

proc univariate data=test1 noprint ;
by group;
histogram y/ grid normal name='MyHist' vscale=proportion hoffset=10 vaxis=0 to 1 by .5
endpoints = &lww to &upp by &bww;
run;
%end;
%mend histo;

%histo;

[hbi]

Hi, 

Since your parameters are generated dynamically, it makes sense to trap for certain invalid conditions before passing them to proc univariate. First, I added "options symbolgen mprint;" since that is helpful when looking at log output for dynamically generated variables.

Second, I added debugging code within the do loop that does a few things:

• traps for two invalid conditions (&bww=0 and &lww > &upp), which proc univariate finds acceptable
• outputs the invalid condition to the results pane 

Hope this helps. Have a look at the attached PDF file too. 

%macro histo;

proc sort data=test out=test1;
by group;
run;
 
proc means data=test1 min max range;
by group;
var y;
output out=dishist2(drop=_TYPE_ _FREQ_) min=min max=max range=range;
run;
 
data dishist2;
set dishist2;
bw=(max-min)/10;
lw=floor(min/bw)*bw-(3*bw);
up=floor(((max/bw)+1)*bw)+(3*bw);
run;
 
proc sql noprint ;
select bw ,lw, up
into :bw1 separated by ' ',
:lw1 separated by ' ',
:up1 separated by ' '
from dishist2;
quit;

%let cnt=&sqlobs;

options symbolgen mprint;
%do i=1 %to &cnt;
  
  %let lww= %scan(&lw1,&i);
  %let upp= %scan(&up1,&i);
  %let bww= %scan(&bw1,&i);
  %let bad_flag=0;
  %let bad_reason="";

  /* flag certain invalid conditions */
  %IF %EVAL(&bww=0) %THEN %DO; 
      %let bad_flag=1;
      %let bad_reason=%STR(Cannot execute due to bad parameters: bww = 0);
  %END;
  %ELSE %IF %EVAL(&lww > &upp) %THEN %DO;
      %let bad_flag=2;
      %let bad_reason=%STR(Cannot execute due to bad parameters: lww > upp);
  %END;
  /* if there are other invalid condition that you want to detect, add them here: */
  /*  %ELSE %IF %EVAL( test_for_bad_condition_here ) %THEN %DO;                   */
  /*    %let bad_flag=3;                                                          */
  /*    %let bad_reason=%STR( description_of_bad_condition_here );                */
  /*  %END; */

  %IF %EVAL(&bad_flag=0) %THEN %DO;

    proc univariate data=test1 noprint ;
    by group;
    histogram y/ grid normal name='MyHist' vscale=proportion hoffset=10 vaxis=0 to 1 by .5
    endpoints = &lww to &upp by &bww;
    run;

  %END;
  %ELSE %DO;

    /* output explanation why procedure cannot be executed ... */

    DATA work.debug_table;
      cnt = &cnt;
      lww = &lww;
      upp = &upp;
      bww = &bww;
    RUN;
    
    PROC PRINT DATA=debug_table; 
      TITLE color=red "&bad_reason";
    RUN;

    PROC SQL; DROP TABLE work.debug_table; QUIT;

    TITLE; *reset title;

  %END;

%end;
options nosymbolgen nomprint;

%mend histo;
%histo;