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;
        convert date = date_lead / transformout=(lead 1)
;
        by group; run;

        data data3; set data2;
        if date_lead=. then date_lead='01Jan2013'd;
        run;

        data data4 ; set data3;
        if date < date_lead then
        do until(date=date_lead);
        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;
        create table data5(drop=date_lead count)
        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
        group by a.group,date_lead,quantity
        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) */

Related links:

Continue to next: SAS Cool Tips & Tricks of Proc SQL; How to Rank a variable ?   SAS tutorial home
Back to: Change SAS Table view to see columns names, not labels   Statistics tutorial home