EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

Tips to massage date related data
How to extend weekly data into daily?

Question: LDD(Little Dumb Doctor) used to work on a small project like the following: given a dataset with separate dates(similar to weekly dates), we need to extend those "weekly" dates into daily, by multiplying the corresponding daily share. The tricky part is how can we extend the "weekly" to daily output. There might be more than one way to accomplish this, here is the solution we proposed:

/****************************************************/
PROC import OUT=data1
DATAFILE="directory\cool_tips_date_extension_data_example1.csv"

DBMS=CSV REPLACE ; GETNAMES=yes;
DATAROW=2; guessingrows=3125;
quit;

proc expand data=data1 out=data2(drop=time) method = none;
;
by group; run;

data data3; set data2;
run;

data data4 ; set data3;
output;
date=date+1;
end;
run;

PROC import OUT=data0
DATAFILE="directory\cool_tips_date_extension_data_example2.csv"
DBMS=CSV REPLACE ; GETNAMES=yes;
DATAROW=2; guessingrows=3125; quit;

proc sql;
as select a.*,b.count,
a.quantity*b.count/sum(count) as Quantity_breakup format best8.0
from data4 as a left join data0 as b
on a.date=b.date and a.group=b.group
order by a.group,date ;
quit;
/****************************************************/
Some highly-recommended data visulization books

There might be other approach to do the same extending. For example, you may use intnx function to massage the dates.

intnx('week', '15mar2000'd, 1, 'same'); returns 22MAR2000
intnx('dtweek', '15mar2000:8:45'dt, 1, 'S'); returns 22MAR00:08:45:00
intnx('month', '15mar2000'd, 5, 'same'); returns 15AUG2000
intnx('year', '29feb2000'd, 2, 'same'); returns 28FEB2002
intnx('month', '31aug2001'd, 1, 'same'); returns 30SEP2001
intnx('year', '01mar1999'd, 1, 'same'); returns 01MAR2000
(the first day of the third month of the year)

intnx('semiyear','01jan97'd,1); returns 01JUL97
intnx('month2','01aug96'd,-1); returns 01MAY96
intnx('semimonth2.2','01apr97'd,4); returns 16JUL97
intnx('month','01jan95'd,5,'beginning'); returns 01JUN95
intnx('month','01jan95'd,5,'middle'); returns 15JUN95
intnx('month','01jan95'd,5,'end'); returns 30JUN95
day=date_pos - intnx('year',date_pos,0)+1; /* to get the day of the year (1-365) */