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

Showing results for

Find a Community

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-15-2017 11:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-15-2017 11:17 AM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-15-2017 11:18 AM

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.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-15-2017 11:20 AM

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.