03-29-2018 03:19 AM
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.
03-29-2018 05:11 AM
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.
03-29-2018 07:03 AM
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?