DATA Step, Macro, Functions and more

find maximum day of the sale

Reply
Occasional Contributor
Posts: 5

find maximum day of the sale

How to find maximum sale day of the previous week according to the day the report is run. The date needs to be kept dynamic.

how i can solved this using attached dataset.

 

Valued Guide
Posts: 631

Re: find maximum day of the sale

Posted in reply to Raavi2507

A csv-file is not a dataset, so please post the code used to read the csv-file, so that we start with what you already have.

Frequent Contributor
Posts: 112

Re: find maximum day of the sale

Posted in reply to Raavi2507
data have;
infile datalines dlm=',' dsd;
input InvoiceNo StockCode:$12. Description:$30. Quantity InvoiceDate:ANYDTDTE. UnitPrice:best12.2 CustomerID Country:$20.;
format InvoiceDate date9.;
datalines;
536365,85123A,WHITE HANGING HEART T-LIGHT HOLDER,6,01-12-10 8:26,2.55,17850,United Kingdom
536365,71053,WHITE METAL LANTERN,6,01-13-10 8:26,3.39,17850,United Kingdom
536365,84406B,CREAM CUPID HEARTS COAT HANGER,8,01-14-10 8:26,2.75,17850,United Kingdom
536365,84029G,KNITTED UNION FLAG HOT WATER BOTTLE,6,01-15-10 8:26,3.39,17850,United Kingdom
536365,84029E,RED WOOLLY HOTTIE WHITE HEART.,6,01-16-10 8:26,3.39,17850,United Kingdom
536365,22752,SET 7 BABUSHKA NESTING BOXES,2,01-17-10 8:26,7.65,17850,United Kingdom
536365,21730,GLASS STAR FROSTED T-LIGHT HOLDER,6,01-18-10 8:26,4.25,17850,United Kingdom
536366,22633,HAND WARMER UNION JACK,6,01-19-10 8:28,1.85,17850,United Kingdom
536366,22632,HAND WARMER RED POLKA DOT,6,01-20-10 8:28,1.85,17850,United Kingdom
536367,84879,ASSORTED COLOUR BIRD ORNAMENT,32,01-21-10 8:34,1.69,13047,United Kingdom
536367,22745,POPPY'S PLAYHOUSE BEDROOM ,6,01-22-10 8:34,2.1,13047,United Kingdom
536367,22748,POPPY'S PLAYHOUSE KITCHEN,6,01-23-10 8:34,2.1,13047,United Kingdom
536367,22749,FELTCRAFT PRINCESS CHARLOTTE DOLL,8,01-24-10 8:34,3.75,13047,United Kingdom
536367,22310,IVORY KNITTED MUG COSY ,6,01-25-10 8:34,1.65,13047,United Kingdom
536367,84969,BOX OF 6 ASSORTED COLOUR TEASPOONS,6,01-26-10 8:34,4.25,13047,United Kingdom
536367,22623,BOX OF VINTAGE JIGSAW BLOCKS ,3,01-27-10 8:34,4.95,13047,United Kingdom
536367,22622,BOX OF VINTAGE ALPHABET BLOCKS,2,01-28-10 8:34,9.95,13047,United Kingdom
536367,21754,HOME BUILDING BLOCK WORD,3,01-29-10 8:34,5.95,13047,United Kingdom
536367,21755,LOVE BUILDING BLOCK WORD,3,01-30-10 8:34,5.95,13047,United Kingdom
536367,21777,RECIPE BOX WITH METAL HEART,4,01-31-10 8:34,7.95,13047,United Kingdom
;
run;

proc sql;
create table want as
select *, (select max(InvoiceDate) from
										(select InvoiceDate from
																( select InvoiceDate,sum(UnitPrice) as _price from have 
																					where InvoiceDate between h.InvoiceDate-1 and h.InvoiceDate-8 
																					group by InvoiceDate
																)
											group by InvoiceDate having max(_price)
										)
			) format date9. as Last_week_max_sale
from have h;
quit;

This is a long file, please post small examples or sample input output, that way it becomes easy for us to solve an issue.

Please let us know if it helped. 

Occasional Contributor
Posts: 5

Re: find maximum day of the sale

Posted in reply to Satish_Parida

this code not working may be we should used macro as date keep dynamic.

 

Super User
Posts: 10,623

Re: find maximum day of the sale

Posted in reply to Raavi2507

Post your data as is, and the code you have, and show where the results differ from your expectations. Then we can help you with code that fits your data and will work.

You have been shown repeatedly how to post data.

Without proper example data, it's all guesswork.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,939

Re: find maximum day of the sale

Posted in reply to Raavi2507

First, let's rule that out as a possibility.  If your code doesn't work, you can never turn it into a macro and hope that it now will work.  You need working code first.

 

You need to clarify what "previous week" means.  Seven days relative to the date you run the program?  (Would today's date be included or excluded?)  Seven days relative to the InvoiceDate?  (Again, would the invoice date be included or excluded?)

 

What identifies the observations you will consider?  For example, do you want all observations in the previous week?  All observations for the same customer?

Ask a Question
Discussion stats
  • 5 replies
  • 189 views
  • 4 likes
  • 5 in conversation