topic Re: Converting Character Quarterly Date to Numerical One in SAS Programming

Converting Character Quarterly Date to Numerical One

Jaaa — Mon, 30 Oct 2017 23:05:43 GMT

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

30jun1980

30sep1980

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

1980Q2

1980Q3

1980Q4

....

Any helpful comments will be grateful!

Re: Converting Character Quarterly Date to Numerical One

kiranv_ — Mon, 30 Oct 2017 22:06:40 GMT

what is the error you get.

Re: Converting Character Quarterly Date to Numerical One

Jaaa — Mon, 30 Oct 2017 22:11:52 GMT

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
.....

Re: Converting Character Quarterly Date to Numerical One

kiranv_ — Mon, 30 Oct 2017 22:18:28 GMT

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

Re: Converting Character Quarterly Date to Numerical One

Jaaa — Mon, 30 Oct 2017 22:22:18 GMT

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
.....

Re: Converting Character Quarterly Date to Numerical One

kiranv_ — Mon, 30 Oct 2017 22:32:41 GMT

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
;

Re: Converting Character Quarterly Date to Numerical One

Jaaa — Mon, 30 Oct 2017 22:41:23 GMT

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

Re: Converting Character Quarterly Date to Numerical One

Reeza — Mon, 30 Oct 2017 22:48:40 GMT

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?

Re: Converting Character Quarterly Date to Numerical One

Jaaa — Mon, 30 Oct 2017 23:02:07 GMT

Thank you for your reply!

rdate (numerical and DATE9.)

31mar1980

30jun1980

30sep1980

31dec1980

.....

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

date

1980Q1

1980Q2

1980Q3

1980Q4

......

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

Re: Converting Character Quarterly Date to Numerical One

Reeza — Tue, 31 Oct 2017 00:21:56 GMT

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.;

Re: Converting Character Quarterly Date to Numerical One

Jaaa — Tue, 31 Oct 2017 11:26:51 GMT

Thank you Reeza! It works now!!