topic Re: create variable from proc means to input into another proc means macro in Statistical Procedures

create variable from proc means to input into another proc means macro

capam — Thu, 17 Aug 2017 20:08:58 GMT

With the following macros I create 2 different types of tables using a simple proc means approach. The code works fine.

I need to modify the code to add a column to the TABS_stat_per_axle proc means by dividing the MAX of TABS_stat_per_axle by the MAX of the TABS_stat_slide_risk proc means.

%macro TABS_stat_per_axle(num);

	proc means data=WORK.QUERY_FOR_COUNTS MAX MAXDEC=2;
		where SORT_ORDER = &num;
		title "TABS Statistic - &num";
		Label STATISTIC_ATTRIBUTE_VAL="&num - distance in ft";
		class vehicle_no;
		var STATISTIC_ATTRIBUTE_VAL;
	run;

%mend TABS_stat_per_axle;
%macro TABS_stat_slide_risk(num);

	proc means data=WORK.QUERY_FOR_COUNTS MIN MAX MAXDEC=2;
		where SORT_ORDER = &num;
		title "TABS Statistic - &num";
		Label STATISTIC_ATTRIBUTE_VAL="&num - distance in ft";
		class vehicle_no;
		var STATISTIC_ATTRIBUTE_VAL;
	run;

%mend TABS_stat_slide_risk;
data _null_;
	array TABS_no[26] _temporary_ (1113:1138)
	;
	call execute('proc sql;');

	do i=1 to dim(TABS_no) while (TABS_no(i) ne .);
	if TABS_no(i)=1129 then
		call execute(cats('%nrstr(%TABS_stat_slide_risk)(',TABS_no(i),')'));
		else
		call execute(cats('%nrstr(%TABS_stat_per_axle)(',TABS_no(i),')'));
	end;

	call execute('quit;');
run;

Re: create variable from proc means to input into another proc means macro

ballardw — Thu, 17 Aug 2017 22:20:27 GMT

I have to say I am not seeing a good reason to request the exact same statistic (max) once by itself and once with max and min in two different macros.

Add an output out= in the proc means used in your TABS_stat_slide_risk macro then do the operation in a following data step.

I think a single call to proc Report is likely able to do everything you are attempting here.

You really need to provide example input data and what you want the final result to be. You are attempting in your call execute statements to run Proc Means in the middle of a Proc SQl statement and that just flat out is not going to work.

Re: create variable from proc means to input into another proc means macro

capam — Wed, 23 Aug 2017 12:06:12 GMT

Hi Ballardw,

The portion of code below is in a macro that partially solved my issue. Thanks for your comment.

	proc means noprint data=StatTable MAX MAXDEC=2;
		where SORT_ORDER = &num;
		title "TABS Statistic - &num";
		Label STATISTIC_ATTRIBUTE_VAL="&num - distance in ft";
		class vehicle_no;
		var STATISTIC_ATTRIBUTE_VAL ;
		output out=slide&num(drop=_type_ ) max=max;
	run;
	proc sql;
		create table slide&num as
		select A.*, B.max as max1129
		from slide&num A left join slide1129 B
		on A.vehicle_no=B.vehicle_no
		;
	quit;