convert monthly to quarterly data

[econfkw]

I want to convert monthly to quarterly data. Please see the attached file - in csv. The date format is 19260801.

1. I import the file to SAS and name it fama

2. informat the date

data fama1(keep= date RF);

set fama;

informat date yymmdd8.;

run;

proc sort data=fama1;

by date;

run;

proc expand data=fama1 out=temp2 from=month to=qtr;

id date;

convert RF = interest / method = aggregate observed=average;

run;

3. an error message pops up:

ERROR: The ID variable value date=19260801 is extreme and invalid at observation number 2 in dataset work.fama1

what went wrong? The date informat?

[Reeza · Accepted solution]

Do you get the same error if you import as a date directly?

I can't test it because I don't have an ETS license apparently.

	data WORK.fama	;
	%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
	infile 'your path here\famafrenchmonthly.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

	informat date yymmdd8. ;
	informat SMB best32. ;
	informat HML best32. ;
	informat RF best32. ;
	format date date9. ;
	format SMB best12. ;
	format HML best12. ;
	format RF best12. ;
	input
	date
	SMB
	HML
	RF
	;
	if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variabl

*/

run;

[Haikuo]

What version of SAS you are using? It runs fine on 9.3

15         proc expand data=fama1 out=temp2 from=month to=qtr;

16         id date;

17         convert RF = interest / method = aggregate observed=average;

18         run;

NOTE: There were 1032 observations read from the data set WORK.FAMA1.

NOTE: The data set WORK.TEMP2 has 344 observations and 2 variables.

NOTE: PROCEDURE EXPAND used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

Here is the first 10 obs from temp2 table:

1	1926:3	0.233
2	1926:4	0.303
3	1927:1	0.27
4	1927:2	0.27
5	1927:3	0.264
6	1927:4	0.227
7	1928:1	0.289
8	1928:2	0.284
9	1928:3	0.304
10	1928:4	0.282

Haikuo

[econfkw]

I have 9.1. Reeza's way works; I don't know why mine doesn't. Probably it's better to import a csv file by writing a code rather than using the sas interface.

thanks