Macro to do the following?

[BE1109nb]

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....

Subcategory

Outlet

Sales

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??

[RW9]

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.

[Kurt_Bremser]

You have no dataset attached.

Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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.

[Reeza]

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;

run;

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

See support.sas.com/training/tutorial for YouTube tutorials. And you didn't attach any files.