DATA Step, Macro, Functions and more

Identifying Year

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Identifying Year

 

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?


Accepted Solutions
Solution
‎03-14-2018 01:57 PM
Trusted Advisor
Posts: 1,312

Re: Identifying Year

Posted in reply to sharonlee

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,312

Re: Identifying Year

Posted in reply to sharonlee

 

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?

Contributor
Posts: 21

Re: Identifying Year

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

Solution
‎03-14-2018 01:57 PM
Trusted Advisor
Posts: 1,312

Re: Identifying Year

Posted in reply to sharonlee

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.

Contributor
Posts: 21

Re: Identifying Year

Thank you for the thorough explanation!
Moderator
Posts: 317

Re: Identifying Year

[ Edited ]
Posted in reply to sharonlee

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 */

 

Contributor
Posts: 21

Re: Identifying Year

Posted in reply to AndrewHowell
This also works!
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 144 views
  • 2 likes
  • 3 in conversation