BookmarkSubscribeRSS Feed
Raavi2507
Calcite | Level 5

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.

 

5 REPLIES 5
andreas_lds
Jade | Level 19

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.

Satish_Parida
Lapis Lazuli | Level 10
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. 

Raavi2507
Calcite | Level 5

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

 

Kurt_Bremser
Super User

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.

Astounding
PROC Star

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?

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
  • 5 replies
  • 1090 views
  • 4 likes
  • 5 in conversation