DATA Step, Macro, Functions and more

MAX value for a day of month

Reply
Contributor
Posts: 21

MAX value for a day of month

Hey.

 

I have a task, which I can't really solve. It's about snacks from "sashelp.snacks".

I have to make a table where I could only find the max values of each months. So basically I would see a monthname, the income (max from the month) and the day, which had the max income.

 

My code is like:

data test.day;
set test.sorted_snack (keep=Day ID);
run;


proc means data=test.sorted_snack max noprint;
class Month;
var Income;
output out=test.inc;
run;

and after that I would merge the two by ID. So I would see the Day, Max inc by Month. But it doesn't work...

Can someone help me?

 

Thank you!

 

p.s.: I can only use datasteps.

PROC Star
Posts: 552

Re: MAX value for a day of month

When you are reffering to the income in sashelp.snacks, do you mean the quantity sold or the quantity multiplied by the retail price of the product?

Contributor
Posts: 21

Re: MAX value for a day of month

I made a new column (income) by multiplying the "quantity sold" with "unit price".

Super User
Super User
Posts: 7,411

Re: MAX value for a day of month

You don't need means, just get your data items correctly assigned, then sort them so max is first, then output that row:

data inter (keep=mon day tot);
  set sashelp.snacks;
  mon=month(date);
  day=day(date);
  tot=qtysold * price;
run;

proc sort data=inter out=want;
  by mon tot day;
run;

data want;
  set want;
  by mon;
  if first.mon then output;
run;
Contributor
Posts: 21

Re: MAX value for a day of month

It's just write out the max by month. But I have 3 years. So I have to distribute the months like e.g. 2011-11, 2012-11, 2013-11.

Super User
Super User
Posts: 7,411

Re: MAX value for a day of month

This is why it is a good idea to post a) test data in the form of a datastep, and b) what your output should look like.  I am just guessing each time:

data inter (keep=yrmon day tot);
  set sashelp.snacks;
  yrmon=catx('-',put(year(date),4.),put(month(date),z2.));
  day=day(date);
  tot=qtysold * price;
run;

proc sort data=inter out=want;
  by yrmon tot day;
run;

data want;
  set want;
  by yrmon;
  if first.yrmon then output;
run;
Contributor
Posts: 21

Re: MAX value for a day of month

I don't know if that is the easiest solution, but I figured it out. Or at leats I have found a solution, which seems to work fine. Smiley Happy

Here it is:

data test.inter (keep= year mon day income);
  set test.sorted_snack;
  day=day(date);
  year=year(date);
  mon=put(date, MONNAME.);
run;

proc sort data=test.inter out=test.want;
  by year mon income day;
run;

data test.want;
  set test.want;
  by year mon;
  if last.mon then output;
  drop year;
run;

proc sort data=test.want out=test.want;
by mon;
run;
Super User
Posts: 10,516

Re: MAX value for a day of month


Derdavos wrote:

I don't know if that is the easiest solution, but I figured it out. Or at leats I have found a solution, which seems to work fine. Smiley Happy

Here it is:


It would help to show exactly what you did first with the SASHelp.snacks data set as we can't duplicate your code without that information.

 

I think this may be similar to your request.

Data have;
   set sashelp.snacks;
   income =  QtySold*price;
   iddate=date;
   format iddate mmddyy10.;
run;

proc summary data=have nway;
   class date;
   format date yymmd7.;
   var income;
   id iddate;
   output out=want max= maxid(income(iddate))=HighestIncome ;
run;

If you have an identification variable then proc means/ summary will can find the max and the value of that ID variable associated with the max (or min) or top 3 and such. The format applied to the base Date variable will tell the procedure to group data by the formatted values (month and year in this case) for the output.

 

Ask a Question
Discussion stats
  • 7 replies
  • 121 views
  • 0 likes
  • 4 in conversation