Date format--quarterly

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Date format--quarterly

Hey folks,

 

It is with a heavy heart that I come crawling here for help with a basic question. But I have spent all afternoon on this and I'm just spinning my tires here. Thank you so much in advance, you all are great.

 

I have two problems.

 

  1. I don't think my date is formatted in a way that makes sense. While "date" is the variable, each observation reads like this: 01OCT0700:00:00. I cannot figure out how to tell SAS that I'd like "yyq." which I believe is the way to say "quarterly data" in SAS.
  2. The reason I want to do this is to run a "proc corr" but only for certain dates. So, I'd want "proc corr where date >=1974q4". If you could help me do this, it would make me smile oh so much.

Here is my code:

 

clear all;
proc import
datafile='C:\Users\Shaft\Documents\France_2015_2016\M2_ETE\Master_Thesis\matthes.csv'
out=work.matthes
dbms=csv
replace;
getnames=yes;
datarow=2;
run;
data matthes;
input date mmddyy8. y y_lag i i_lag pipce pipce_lag;
run;
title 'Fishers Z Transformation';
proc corr data=matthes (where=(date GE '1979q3')) fisher(rho0=.99);
var i i_lag;
run;

 

Thank you so much.

John


Accepted Solutions
Solution
‎05-07-2016 03:14 PM
Super User
Posts: 19,105

Re: Date format--quarterly

[ Edited ]

Ok...well if the dates are correct this would work. You do need to use the date literal or you can convert it to a charater variable using PUT. 

 

data matthes2;
set matthes;

date_num=datepart(date);
format date_part yyq6.;

date_char = put(date_num, yyq6.);
run; title 'Fishers Z Transformation'; proc corr data=matthes2 fisher(rho0=.99); where date_num ge '01Oct1979'd; var i i_lag; run;

OR

title 'Fishers Z Transformation';
proc corr data=matthes2  fisher(rho0=.99);
where date_char ge '1979Q3';
var i i_lag;
run;

 

EDIT: Answer modified to correct format based on @ballardw comment. 

View solution in original post


All Replies
Super User
Posts: 5,362

Re: Date format--quarterly

John,

 

You'll need to take a couple of steps to resolve this.  First, we need to figure out what is actualy in the DATE variable.  To do this, add after the IMPORT step:

 

proc contents data=matthes;

run;

proc print data=matthes (obs=5);

var date;

run;

 

Show the results of the PROC CONTENTS for the variable DATE, and the results of the PROC PRINT.  That will reveal what is actually stored in DATE.

Occasional Contributor
Posts: 9

Re: Date format--quarterly

[ Edited ]

Astounding,

 

You are indeed astounding. Thank you so much for your help.

 

I have attached the results of your commands. I will paste them here as well:

 

The CONTENTS Procedure

 

Data Set Name Observations Member Type Variables Engine Indexes Created Observation Length Last Modified Deleted Observations Protection Compressed Data Set Type Sorted Label   Data Representation   Encoding  
WORK.MATTHES181
DATA7
V90
05/07/2016 19:23:0356
05/07/2016 19:23:030
 NO
 NO
  
WINDOWS_64 
wlatin1 Western (Windows) 

 

Engine/Host Dependent Information Data Set Page Size Number of Data Set Pages First Data Page Max Obs per Page Obs in First Data Page Number of Data Set Repairs ExtendObsCounter Filename Release Created Host Created
65536
1
1
1167
181
0
YES
C:\Users\Shaft\AppData\Local\Temp\SAS Temporary Files\_TD7652_SHAFT-PC_\matthes.sas7bdat
9.0401M0
X64_7PRO

 

Alphabetic List of Variables and Attributes # Variable Type Len Format Informat 1 4 5 6 7 2 3
dateNum8DATETIME.ANYDTDTM40.
iNum8BEST12.BEST32.
i_lagNum8BEST12.BEST32.
pipceNum8BEST12.BEST32.
pipce_lagNum8BEST12.BEST32.
yNum8BEST12.BEST32.
y_lagNum8BEST12.BEST32.

 

 

Fishers Z Transformation

Obs date 1 2 3 4 5
01JAN60:00:00:00
01APR60:00:00:00
01JUL60:00:00:00
01OCT60:00:00:00
01JAN61:00:00:00

-George


sas1.pngsas2.png
Super User
Posts: 19,105

Re: Date format--quarterly

Can you convert your Excel file to a CSV and import it that way? 

 

It it looks like reading file from Excel isn't occurring properly. There are ways around this, but if this is a one time process the CAV method is easiest in my opinion. 

 

You'll get more control over your import when importing a CSV. 

Occasional Contributor
Posts: 9

Re: Date format--quarterly

Hey Reeza!

 

Thanks for your time.

 

I believe my data file is already in .csv.

 

:/

Super User
Posts: 19,105

Re: Date format--quarterly

Yes it is. You have proc import and a partial datastep import, which misses the file statement. Which one did you use? 

 

Use the proc import and get the code from the log. Delete proc import portion of code or comment it out. 

Then modify the code so that it reads correctly. 

 

If you need help with that post a sample of the dataset as well as the data step import code. 

 

Occasional Contributor
Posts: 9

Re: Date format--quarterly

I apoligize, but I think Im a bit lost. Should I keep the "proc import" or get rid of it?

 

clear all;
proc import
datafile='C:\Users\Shaft\Documents\France_2015_2016\M2_ETE\Master_Thesis\matthes.csv'
out=work.matthes
dbms=csv
replace;
getnames=yes;
datarow=2;
run;

 

This is the code I have now that at least imports my data consistently. I did indeed try some kind of "input" stuff to tell SAS what the date was; I shouldnt have included it above.

 

datasample.jpg

 

Above is a snapshot of what my data looks like now.

 

Thank you again so much for your help. Cheering me up big time!

 

John 

Super User
Posts: 19,105

Re: Date format--quarterly

I'm assuming your dates aren't correct as their in the 1960's...

 

1. Use proc import

2. Check log for datastep code to read file

3. Replace proc import with code from step 2

4. Modify code from step 2 for date variable. We would need to see a few lines of the raw data to suggest format.

Occasional Contributor
Posts: 9

Re: Date format--quarterly

Those are indeed the right dates. I have quarterly data from 1960q1 to 2005q1. In my excel file, that is exactly how the dates read. Works in stata that way as well.

Given that these dates are right, is there a command I should do for this? Also, once the date is right (I mean, maybe the date is fine like this), do you have tips on addressing my main concern, which is to be able to do proc corr *conditional* upon a date? E.g. proc corr where date < 1980q1?

Thank you again.
Solution
‎05-07-2016 03:14 PM
Super User
Posts: 19,105

Re: Date format--quarterly

[ Edited ]

Ok...well if the dates are correct this would work. You do need to use the date literal or you can convert it to a charater variable using PUT. 

 

data matthes2;
set matthes;

date_num=datepart(date);
format date_part yyq6.;

date_char = put(date_num, yyq6.);
run; title 'Fishers Z Transformation'; proc corr data=matthes2 fisher(rho0=.99); where date_num ge '01Oct1979'd; var i i_lag; run;

OR

title 'Fishers Z Transformation';
proc corr data=matthes2  fisher(rho0=.99);
where date_char ge '1979Q3';
var i i_lag;
run;

 

EDIT: Answer modified to correct format based on @ballardw comment. 

Occasional Contributor
Posts: 9

Re: Date format--quarterly

[ Edited ]

EDIT: These errors were just due to a small typo on the previous page! Your solution worked like a charm! I will accept it on the other page.

 

Thank you so much. Im giving you a big virtual hug.

 

John

 

Thanks again for your help so far.  Really.

 

I'm working autonomously to figure out why, but here is the error i get:

 

181 rows created in WORK.MATTHES from
C:\Users\Shaft\Documents\France_2015_2016\M2_ETE\Master_Thesis\matthes.csv.

 

NOTE: WORK.MATTHES data set was successfully created.
NOTE: The data set WORK.MATTHES has 181 observations and 7 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds


2975 data matthes2;
2976 set matthes;
2977
2978 date_num=datepart(date);
2979 format date_part yyq6.;
2980 date_char = put(date_num, yy6.);
----
48
ERROR 48-59: The format YY was not found or could not be loaded.

2981 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MATTHES2 may be incomplete. When this step was stopped there were 0
observations and 10 variables.
WARNING: Data set WORK.MATTHES2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

 

It seems it didn't like format date_part yyq6.;

Super User
Posts: 11,121

Re: Date format--quarterly

You missed the Q in this line:
date_char = put(date_num, yy6.);
assuming you meant
date_char = put(date_num, yyq6.);
Occasional Contributor
Posts: 9

Re: Date format--quarterly

Yep, that was it. I mentioned it above and still accepted the post as a solution Smiley Very Happy

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 563 views
  • 0 likes
  • 4 in conversation