DATA Step, Macro, Functions and more

Loop in Macro

Regular Contributor
Posts: 181

Loop in Macro

Hi SAS Experts -

I have a macro that calculates acceptable range of a variable. An acceptable range is defined by :

Lower Limit = Q1 - 1.5*(Q3-Q1)

Upper Limit = Q3 + 1.5*(Q3-Q1)

It's a boxplot method of calculating outliers. The macro is working fine. But it is inefficient in terms of its processing as it calculates outliers for each variable in a loop and then capping values. I want proc univariate to be run for all the variables (not in loop) and save output in a dataset and then capping for variables using IF THEN at one time only.

Code : -

options mprint symbolgen;

%macro outliers(input=, vars=, output= );

data &output;

set &input;


%let n=%sysfunc(countw(&vars));

%do i= 1 %to &n;

%let val = %scan(&vars,&i);

/* Calculate the quartiles and inter-quartile range using proc univariate */

proc univariate data=&output noprint;

var &val;

output out=temp QRANGE= IQR Q1= First_Qtl Q3= Third_Qtl;


/* Extract the upper and lower limits into macro variables */

data _null_;

set temp;

call symput('QR', IQR);

call symput('Q1', First_Qtl);

call symput('Q3', Third_Qtl);


%let ULimit=%sysevalf(&Q3 + 1.5 * &QR);

%let LLimit=%sysevalf(&Q1 - 1.5 * &QR);

/* Final dataset excluding outliers*/

data &output;

set &output;

if &val < &Llimit then &val = &Llimit;

if &val > &Ulimit then &val = &Ulimit;




%outliers(Input=abcd, Vars = a, output= test);


Thanks in anticipation!

Trusted Advisor
Posts: 1,129

Re: Loop in Macro

If we are using univariate we could calculate the statistics of only one variable at a time. So the method you are following is correct.

Valued Guide
Posts: 3,208

Re: Loop in Macro

SAS procs are designed to handle a lot of variables in one run on the data.

Even the outputdataset of Univariate  Base SAS(R) 9.4 Procedures Guide: Statistical Procedures, Second Edition  It ends with mentioning two variables being processed each getting different suffixes.

You are starting your question with "you are having a macro" There could be a confusion there.

Macro-s in SAS are source text base ones (type 2) and not functional processes (type 3) or recorded keyboard/mouse actions (type 1). If you are trying to use macro-s as you used that with that word in Excel or any other programming language leave that behind you and program your logic understanding SAS from scratch

Just another question as you are trying to build boxplots

Did you check whether your question is easily solved by the many procs (modern word would be now packages) that are around .

---->-- ja karman --<-----
Super User
Posts: 17,829

Re: Loop in Macro

For part 1 switch to proc means.  I don't think you need a macro for this at all, but perhaps Jaap's paper has illustrated it better than I can here.

Here's two different ways of generating the stats for all variables at once:

proc means data=sashelp.class stackods n p25 p75 qrange median;

var age weight height;

ods output summary=want1;

output out=want2 n= p25= p75= qrange= median=/autoname;


Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation