BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
econfkw
Calcite | Level 5


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?


1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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
Onyx | Level 15

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

econfkw
Calcite | Level 5

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

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