topic Re: find maximum day of the sale in SAS Programming

find maximum day of the sale

Raavi2507 — Thu, 29 Mar 2018 06:14:20 GMT

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.

Re: find maximum day of the sale

andreas_lds — Thu, 29 Mar 2018 06:33:25 GMT

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.

Re: find maximum day of the sale

Satish_Parida — Thu, 29 Mar 2018 07:19:37 GMT

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.

Re: find maximum day of the sale

Raavi2507 — Thu, 29 Mar 2018 09:06:05 GMT

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

Re: find maximum day of the sale

Kurt_Bremser — Thu, 29 Mar 2018 09:11:46 GMT

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.

Re: find maximum day of the sale

Astounding — Thu, 29 Mar 2018 11:03:26 GMT

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?