BookmarkSubscribeRSS Feed
BE1109nb
Calcite | Level 5

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Super User

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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 649 views
  • 0 likes
  • 4 in conversation