Forecasting using SAS Forecast Server, SAS/ETS, and more

convert monthly to quarterly data

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

convert monthly to quarterly data


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?


Attachment

Accepted Solutions
Solution
‎09-12-2012 10:38 AM
Super User
Posts: 19,855

Re: convert monthly to quarterly data

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;

View solution in original post


All Replies
Solution
‎09-12-2012 10:38 AM
Super User
Posts: 19,855

Re: convert monthly to quarterly data

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;
Respected Advisor
Posts: 3,156

Re: convert monthly to quarterly data

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:

11926:30.233
21926:40.303
31927:10.27
41927:20.27
51927:30.264
61927:40.227
71928:10.289
81928:20.284
91928:30.304
101928:40.282

Haikuo

Contributor
Posts: 23

Re: convert monthly to quarterly data

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 2159 views
  • 0 likes
  • 3 in conversation