Date: Mon, 2 Feb 2004 08:42:13 -0500 Howard Schreier "SAS(r) Discussion" Howard Schreier Re: Generating a date series

Let's deomonstrate with two weeks instead of five years.

You probably want to omit holidays as well as weekends. The best way to do that is to build a table as a format:

proc format; value holiday '25dec2003'd = 'Y' '01jan2004'd = 'Y' other = 'N' ; quit;

The next step generates test data (two companies, two weeks):

data demo; do company = 'A', 'B'; do date = '21dec2003'd to '03jan2004'd; format date date9.; if weekday(date) not in (1,7) and put(date,holiday.) ^= 'Y' and ranuni(1)>0.3 then do; VarA = round(ranuni(1),0.1); VarB = round(ranuni(1),0.1); output; end; end; end; run;

Output:

Obs company date VarA VarB

1 A 22DEC2003 0.4 0.3 2 A 23DEC2003 1.0 0.5 3 A 24DEC2003 0.0 0.1 4 A 26DEC2003 0.9 0.1 5 A 30DEC2003 1.0 0.2 6 A 31DEC2003 0.4 0.6 7 A 02JAN2004 0.8 0.6 8 B 22DEC2003 0.7 0.5 9 B 23DEC2003 0.9 0.6 10 B 26DEC2003 0.7 0.2 11 B 30DEC2003 0.9 0.6 12 B 02JAN2004 0.4 0.2

To generate observations with missing values for days when a particular stock did not trade, first generate a company x date "shell":

data shell / view=shell; set demo; by company; keep company date; if last.company then do date = '21dec2003'd to '03jan2004'd; if weekday(date) not in (1,7) and put(date,holiday.) ^= 'Y' then output; end; run;

Output:

Obs company date

1 A 22DEC2003 2 A 23DEC2003 3 A 24DEC2003 4 A 26DEC2003 5 A 29DEC2003 6 A 30DEC2003 7 A 31DEC2003 8 A 02JAN2004 9 B 22DEC2003 10 B 23DEC2003 11 B 24DEC2003 12 B 26DEC2003 13 B 29DEC2003 14 B 30DEC2003 15 B 31DEC2003 16 B 02JAN2004

Now fill in the actual data:

data filled_in; merge shell demo; by company date; run;

Output:

Obs company date VarA VarB

1 A 22DEC2003 0.4 0.3 2 A 23DEC2003 1.0 0.5 3 A 24DEC2003 0.0 0.1 4 A 26DEC2003 0.9 0.1 5 A 29DEC2003 . . 6 A 30DEC2003 1.0 0.2 7 A 31DEC2003 0.4 0.6 8 A 02JAN2004 0.8 0.6 9 B 22DEC2003 0.7 0.5 10 B 23DEC2003 0.9 0.6 11 B 24DEC2003 . . 12 B 26DEC2003 0.7 0.2 13 B 29DEC2003 . . 14 B 30DEC2003 0.9 0.6 15 B 31DEC2003 . . 16 B 02JAN2004 0.4 0.2

On Sun, 1 Feb 2004 21:18:20 -0800, Microstructure <randistan69@HOTMAIL.COM> wrote:

>My data is arranged in the following way > >date comp VarA VarB... >jan199 A >. >. >Dec 3103 A >Jan199 B >. >. >Dec3103 B >and so forth > >there are some days for some companies on which their stocks were not >traded >I want to generate a date series from Jan 1 1999 to Dec 31 2003, for >all days for each company (excluding saturdays and Sundays)...I tried >using the date functions but they do not seem to be relevant for what >I want to do. > Please help

Back to: Top of message | Previous page | Main SAS-L page