Cleaning data to do time series analysis.

 I will use Tableau’s sample data set coffee-chain to demonstrate how to clean up data to perform time series analysis.

 /*importing data using proc import*/

 proc import out= work.coffee datafile= "C:/Users/lgh2811/Desktop/time_series/coffee.csv" dbms=csv replace; getnames=yes; datarow=2; run;

 /*print first 10 observations of the dataset*/

 proc print data=coffee(obs=10); run;

 /*please note that date is in the datetime format which includes the date part and time part we want to use month as id for time series; so we need to keep only the date part of the date variable. we can do this by using the datepart() function. Intck('month',date1,date2) calculates the number of months between the two dates. We need to add 1 so that the first month is 1 instead of 0.*/


 data coffee;
 set coffee; month=intck('month','01JAN2012'd,datepart(date))+1;
 run;


 /*Next, we need to group the records by the month number. We are interested in the sum of sales by month; so we can use the proc sql statement below to do that.*/

 proc sql; create table coffee1 as select month,sum(sales) as sales from coffee group by month;
 quit;

 /*printing a sample to make sure the table is the way we wanted*/
 proc print; run;