BookmarkSubscribeRSS Feed
Derdavos
Obsidian | Level 7

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.

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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?

Derdavos
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Derdavos
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Derdavos
Obsidian | Level 7

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

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

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1583 views
  • 0 likes
  • 4 in conversation