Time-series analysis in SAS

Importing and changing data

FILENAME REFFILE '//nlws0844/mjacobs$/StatisticsPlatform/Website/ELearning/Workshops/Time-Series/Data/Deceased.xlsx';
PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT;
GETNAMES=YES;
RUN;
/* Delete row 
/** Content of the data **/
proc freq data=import nlevels;table year*week;run;
/** DATA WRANGLING **/
/*  Create a date variable */
data import;set import;yearnum=input(year,7.);run;
data import;set import;
date=intnx('week',mdy(1,1,year),week-1,'e');
format date date9.;
run;
proc freq data=import nlevels;table date;run;
/* Delete duplicates - make sure no artefacts are born */
PROC SQL;
CREATE TABLE WANT AS
SELECT date, 
SUM(total_all) 			    AS total_all,
SUM(total_female) 		  AS total_female,
SUM(total_male) 		    AS total_male,
SUM('0_65_all'n) 		    AS '0_65_all'n,
SUM('0_65_female'n) 	  AS '0_65_male'n,
SUM('0_65_male'n) 		  AS '0_65_female'n,
SUM('65_80_all'n) 		  AS '65_80_all'n,
SUM('65_80_male'n) 		  AS '65_80_male'n,
SUM('65_80_female'n) 	  AS '65_80_female'n,
SUM('80_more_all'n) 	  AS '80_more_all'n,
SUM('80_more_male'n) 	  AS '80_more_male'n,
SUM('80_more_female'n) 	AS '80_more_female'n
FROM import
GROUP BY date ;
QUIT;
proc freq data=want nlevels;table date;run;
/* From week to Month, since Week is a horrible metric to work with */
proc expand data=want out=month from=week to=month;
convert total_all total_female total_male 
'0_65_all'n '0_65_male'n '0_65_female'n 
'65_80_all'n '65_80_male'n '65_80_female'n
'80_more_all'n '80_more_male'n '80_more_female'n;
id date;run;
/* From Wide to Long */
proc transpose data=month out=monthlong(rename=(_NAME_=Variable COL1=Value));
by date;
run;