topic Re: MAX value for a day of month in SAS Programming

MAX value for a day of month

Derdavos — Fri, 28 Apr 2017 08:56:10 GMT

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.

Re: MAX value for a day of month

PeterClemmensen — Fri, 28 Apr 2017 09:04:31 GMT

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?

Re: MAX value for a day of month

RW9 — Fri, 28 Apr 2017 09:06:56 GMT

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;

Re: MAX value for a day of month

Derdavos — Fri, 28 Apr 2017 09:14:30 GMT

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

Re: MAX value for a day of month

Derdavos — Fri, 28 Apr 2017 09:25:04 GMT

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.

Re: MAX value for a day of month

RW9 — Fri, 28 Apr 2017 09:27:53 GMT

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;

Re: MAX value for a day of month

Derdavos — Fri, 28 Apr 2017 09:39:48 GMT

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. 🙂

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;

Re: MAX value for a day of month

ballardw — Fri, 28 Apr 2017 16:07:12 GMT

@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. 🙂

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.