I have data set with the date and no of sales for that particular date, I need create report to see how many days took for every thousand sales,
Example:
Date NoOfSales
01Jan17 200
02Jan17 350
03Jan17 350
04Jan17 200
05Jan17 100
06Jan17 400
07Jan17 300
08Jan17 200
09Jan17 200
.
.
.
.
and so on
from this table i need to create a table on what date it crossed 1000, 2000, 3000 sales and soon
here
04Jan17 1000
08Jan17 2000
?
Please suggest he how we can achieve this in PROC SQL or DATA step ?
I would use a data step:
data want; set have; cum_sales+NoOfSales; threshold=floor(cum_sales/1000)*1000; if threshold ne lag(threshold) and threshold>0; run;
Post test data in the form of a datastep. As such untested:
data want; set have; retain tot group; if _n_=1 then do; tot=noofsales; group=1000; end; else tot=sum(tot,noofsales); if noofsales >= 1000 then do; output; group=sum(group,1000); noofsales=0; end; run;
I would use a data step:
data want; set have; cum_sales+NoOfSales; threshold=floor(cum_sales/1000)*1000; if threshold ne lag(threshold) and threshold>0; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.