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?

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

Posted in reply to BE1109nb

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

Posted in reply to BE1109nb

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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

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

Posted in reply to BE1109nb

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.