topic Re: Converting Dates for Proc X11 in SAS Programming

Converting Dates for Proc X11

saieedkhalil — Wed, 01 Mar 2017 13:41:28 GMT

Hi Everyone,

I have a very simple data set I am trying to deseasonalize. The data is quarterly. There are two columns in the data set.

Imported from excel, Column 1 is Period, and Column Two is Total Taxes. Those dates repeat all the way to 2016.

03/31/1994

7823982

06/30/1994

9398529

09/30/1994

8126101

12/31/1994

7962473

I found this generic Proc X11 code online:

proc x11 data=dataset;
monthly date=date;/*needs to be in date9. format*/
tables d10 d11 d12 d13;
output out=output_dataset b1=originald10=seasonald11=adjustedd12=trendd13=irreg;
var variable;/*variable is the variable to be analysed*/
run;

But the thing is my dates need to be converted to a date9. format & the code needs to be adjusted for quarterly data, not monthly. I really appreciate any help I can get with this. I am a bit of a noob.

Thanks,
Saieed.

Re: Converting Dates for Proc X11

nehalsanghvi — Wed, 01 Mar 2017 14:48:54 GMT

I am not familiar with the x11 proc but its documentation indicates you can use the Quarterly statement if your input data are quarterly. You do not have to use the Monthly statement.

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_x11_sect005.htm

And looking at the date option on the Quarterly statement, it does not say that the date has to be in a specific format. It only has to contain SAS date values. So as long as your date variable contains SAS dates and it is not a character date variable, you should be fine. See details on Quarterly statement here:

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_x11_sect015.htm

Re: Converting Dates for Proc X11

Kurt_Bremser — Wed, 01 Mar 2017 14:49:20 GMT

If your column 1 is of type character, you need to create a SAS date variable from it:

datevar = input(column1,mmddyy10.);
format datevar date9.;

Then use datevar in place of column1.

Re: Converting Dates for Proc X11

saieedkhalil — Wed, 01 Mar 2017 17:30:24 GMT

Mr. Kurt,

Thank you for your reply, I've replaced all my date values with 1994q1 all the way to 2016q2. How do I format that into a date9. format to get this going?

Thanks again,

Saieed

Re: Converting Dates for Proc X11

saieedkhalil — Wed, 01 Mar 2017 17:33:03 GMT

I tried this:

DATANYTaxes;
SET NYTaxes;
datevar = input(date,yyyyQq.);
format datevar date9.;

and got this response:

68 datevar = input(date,yyyyQq.);

_______

485

NOTE 485-185: Informat YYYYQQ was not found or could not be loaded.

PS My columns are date and TotalNYSTaxes respectively, with dates from 1994q1 to 2016q2/

Re: Converting Dates for Proc X11

Kurt_Bremser — Thu, 02 Mar 2017 08:35:53 GMT

@saieedkhalil wrote:

I tried this:

DATANYTaxes;
SET NYTaxes;
datevar = input(date,yyyyQq.);
format datevar date9.;

and got this response:

68 datevar = input(date,yyyyQq.);

_______

485

NOTE 485-185: Informat YYYYQQ was not found or could not be loaded.

PS My columns are date and TotalNYSTaxes respectively, with dates from 1994q1 to 2016q2/

Since a SAS data value is a number of days from a given starting date (01jan1960), SAS only has informats that contain also day values. formats that do not display a complete data value have no equivalent informat.

So you need to convert your YYYYqQ string into something that contains a complete date, and then you can use a date informat:

length date_int $10;
format datevar yymmddd10.;
date_int = substr(date,1,4) !! '-' !! put(input(substr(date,6,1),1.)*3,z2.) !! '-01';
datevar = input(date_int,yymmdd10.);
datevar = intnx('month',datevar,0,'end');

The final statement sets the date to the last day of the quarter, as I guess this would be more useful.

You can then use the YYQ6. format to display the date as a quarter.