topic Re: Macro to do the following? in SAS Procedures

Macro to do the following?

BE1109nb — Wed, 15 Mar 2017 15:14:24 GMT

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

Re: Macro to do the following?

RW9 — Wed, 15 Mar 2017 15:17:29 GMT

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.

Re: Macro to do the following?

Kurt_Bremser — Wed, 15 Mar 2017 15:18:16 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Macro to do the following?

Reeza — Wed, 15 Mar 2017 15:20:41 GMT

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.