Help using Base SAS procedures

Macro to do the following?

Occasional Learner
Posts: 1

Macro to do the following?

OK…. Let me simplify, simplify and let’s see if you can assist me…or point me in the right direction:


Say for example you have a "three column" spreadsheet in Excel....






I want to write a macro that will perform the following over the attached dataset:


For subcategory1, for Outlet1:

Calculate mean, median and standard deviation for Sales greater than $1

Take natural logarithm of all Sales greater than $1

any Sales greater than (mean + 4*standard deviation) redefine as (mean + 4*standard deviation)

redefine all negative or missing values to the calculated mean.


Loop to next: For subcategory1, for Outlet2:

Do this for every Outlet….

Do this for every Subcategory


So, the end will be a new “column” in the spreadsheet named ZSales…this is the new defined Sales by the macro…


Is this possible??? May I ask you to help me write this??



Super User
Super User
Posts: 9,599

Re: Macro to do the following?

This is not an Excel forum.  If you want help on how to do this in SAS, then present some test data, in the form of a datastep (as structure is important).  And present what you want the output to look like.

SAS datasteps/procedures are already loops, they process the observations 1 by 1, so to get mean and such like a simple proc means call will work.

Super User
Posts: 10,280

Re: Macro to do the following?

You have no dataset attached.

Use the macro provided in to convert your SAS dataset into a data step that can then be posted in a {i} window here.


What you describe looks, at least at first, like a job for proc means with by group processing, and possibly some additional calculations in a data step.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 23,778

Re: Macro to do the following?

There's no need for a macro. Look at SAS BY group processing. 


Quick example:

Proc sort data=SASHELP.class out=class; by sex;run;

proc means data=class;

By sex;



This looks like homework so I suggest you at least try it rather than ask for someone to do it for you. 


See for YouTube tutorials. And you didn't attach any files. 

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