DATA Step, Macro, Functions and more

Converting Character Quarterly Date to Numerical One

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Converting Character Quarterly Date to Numerical One

[ Edited ]

Hi SAS users,

 

I'd like to extract quarterly date in numerical type from numerical daily date. However, It just produces the quarters (variable is date) as the same observations over all time periods. Thus, I wonder would you please have a look for me? Many thanks in advance!!

 

Here is the input data:

 

rdate (numerical and DATE9.)

31mar1980

31mar1980

31mar1980

31mar1980

30jun1980

30jun1980

30jun1980

30sep1980

30sep1980

30sep1980

30sep1980

30sep1980

31dec1980

31dec1980

31dec1980

31dec1980

.....

 

Here is the code: 

 data MMIO_CUSIP;
  set MMIO_CUSIP;
  month=month(rdate);
  year=year(rdate);
  q = qtr(rdate);
  date = put(q,YYQ6.);
  format date YYQ6.;
  run;

 

I wanna it to have the numerical quarterly format like this:

 

date

1980Q1

1980Q1

1980Q1

1980Q2

1980Q2

1980Q2

1980Q3

1980Q3

1980Q3

1980Q4

1980Q4

1980Q4

....

 

Any helpful comments will be grateful! 


Accepted Solutions
Solution
‎10-31-2017 07:26 AM
Super User
Posts: 23,928

Re: Converting Character Quarterly Date to Numerical One

You just change the format you don't need any calculations. 

 

format rdate yyq6.;

*if you want a new variable;
qdate=rdate;
format qdate yyq6.;

View solution in original post


All Replies
PROC Star
Posts: 548

Re: Converting Character Quarterly Date to Numerical One

what is the error you get.

Contributor
Posts: 20

Re: Converting Character Quarterly Date to Numerical One

It just produces the quarters as the same observations over all time periods. Might you know why did it happen like that? 

date 
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
1960Q1
.....
PROC Star
Posts: 548

Re: Converting Character Quarterly Date to Numerical One

when ever you see your data is with 1960, it often raises a flag something is not right. show your input data please

Contributor
Posts: 20

Re: Converting Character Quarterly Date to Numerical One

Sure! Here we go: Date is from 1980Q1 - 2016Q4. Many thanks indeed!

 

rdate
31mar1980
31mar1980
31mar1980
31mar1980
30jun1980
30jun1980
30jun1980
30sep1980
30sep1980
30sep1980
30sep1980
30sep1980
31dec1980
31dec1980
31dec1980
31dec1980
31dec1980
.....
PROC Star
Posts: 548

Re: Converting Character Quarterly Date to Numerical One

[ Edited ]

you are applying date format on extracted quarter value ( whose value will be 1 2 3 or 4)and hence the errors. try applying format on the date rather than quarter value.

data abc;
input date:date9.;
format date YYQ6.;
datalines;
31mar1980
31mar1980
30jun1980
30jun1980
30jun1980
30sep1980
30sep1980
30sep1980
30sep1980
30sep1980
31dec1980
31dec1980
31dec1980
31dec1980
31dec1980
; 

 

Contributor
Posts: 20

Re: Converting Character Quarterly Date to Numerical One

[ Edited ]

 

Thank you for your post! I tried this but it presents errors about the date formats. Do you possibly know why it is? Many thanks! 

484   data MMIO_CUSIP;
485    set MMIO_CUSIP;
486    month=month(rdate);
487    year=year(rdate);
488    q = qtr(rdate);
489    date = put(q,YYQ6.);
490   input date: date9.;
                  ------
                  48
ERROR 48-59: The informat $DATE was not found or could not be loaded.

491   format date YYQ6.;
                  -----
                  48
ERROR 48-59: The format $YYQ was not found or could not be loaded.

492    run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set MMIO_CUSIP may be incomplete.  When this step was stopped there
         were 0 observations and 23 variables.
WARNING: Data set MMIO_CUSIP 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

 

Super User
Posts: 23,928

Re: Converting Character Quarterly Date to Numerical One

What's the format and type on your variable: rdate?

What does it currently look like and what do you want it to look like?

Contributor
Posts: 20

Re: Converting Character Quarterly Date to Numerical One

[ Edited ]

Thank you for your reply! 

 

rdate (numerical and DATE9.)

31mar1980

31mar1980

31mar1980

31mar1980

30jun1980

30jun1980

30jun1980

30sep1980

30sep1980

30sep1980

30sep1980

30sep1980

31dec1980

31dec1980

31dec1980

31dec1980

....

 

I wanna convert the above format to the numerical quarterly type like this:

 

 date

1980Q1

1980Q1

1980Q1

1980Q2

1980Q2

1980Q2

1980Q3

1980Q3

1980Q3

1980Q4

1980Q4

1980Q4

......

Do you possibly know how to achieve that? Many thanks!!

Solution
‎10-31-2017 07:26 AM
Super User
Posts: 23,928

Re: Converting Character Quarterly Date to Numerical One

You just change the format you don't need any calculations. 

 

format rdate yyq6.;

*if you want a new variable;
qdate=rdate;
format qdate yyq6.;
Contributor
Posts: 20

Re: Converting Character Quarterly Date to Numerical One

Thank you Reeza! It works now!! 

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 251 views
  • 0 likes
  • 3 in conversation