Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Macro to do the following?

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-15-2017 11:14 AM
(877 views)

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

**SAS Innovate 2025** is scheduled for May 6-9 in Orlando, FL. Sign up to be **first to learn** about the agenda and registration!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Ready to level-up your skills? Choose your own adventure.