Identifying Year

[sharonlee]

Hi,

I'm having a tough time selecting my data year. I want to include only those years that are greater than 2014.

My original_date variable is of this format: 2014-06-26 14:31:24.0000000

This is my code: 

data new;
set old;

original_date_sas= input(original_date,anydtdte10.);

format original_date_sas mmddyy10.;

fiscal_year = intnx('YEAR.04',original_date_sas,0); /*fiscal year starts april 1 (month 4) */
format fiscal_year year.;

if fiscal_year GE 2014; /*anything starting from 2014 */

run;

The problem is "if fiscal_year GE 2014; " doesn't work!  It still keeps records that are earlyer than 2014. I have tried COMPRESS and  LEFT when creating original_date_sas,  and I put "2014"d., when specify the year but doesn't work. Any suggestions?

[mkeintz]

Is your original value, such as 2014-06-26 14:31:24.0000000, a character variable.  Or is it just what the raw data looks like?

[sharonlee]

That's what the raw data are, and yes, SAS considers it a character variable.

[mkeintz · Accepted solution]

Then

1. Don't use anydtdte. format.  Use yymmdd10., which will read the first 10 characters of ORIGINAL_DATE.
   
   
2. INTNX produces a DATE, not a YEAR, so you need to use the YEAR function applied to the date:

data new;
  set old;
  original_date_sas= input(original_date,yymmdd10.);
  format original_date_sas date9.;

  fiscal_year=year(intnx('year.04',original_date_sas,0));

  if fiscal_year GE 2014; /*anything starting from 2014 */
run;

The INTNX function adds zero year.04 time spans to the date, and then aligns the resulting date to the beginning of the span.  So all the dates from 01apr2014 through 31mar2015 generate 01apr2014.  The year function gets 2014, as you desire.

Note that in the US, fiscal years defined as JAN-DEC   through JUN-MAY usually are identified with the year of the beginning month.  And fiscal years of JUL-JUN through DEC-NOV would be identified with the ending month.  I.e. in general whichever calendar year possesses the majority of months in the fiscal year, is used as the identifier.  That's how the IRS identifies tax years.

[sharonlee]

Thank you for the thorough explanation!

[AndrewHowell]

Even thought fiscal_date is displayed as a year (format), it is actually still a SAS date value (i.e, a count of the number of days since 01Jan1960).

Your SAS date value 2014 is actually 07July1965, which means most if not all of your data will pass the test.

Your filter should be:

if fiscal_year GE "01Jan2014"d; /*anything starting from 2014 */

[sharonlee]

This also works!