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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.