BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sharonlee
Quartz | Level 8

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

6 REPLIES 6
mkeintz
PROC Star

 

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
sharonlee
Quartz | Level 8

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
sharonlee
Quartz | Level 8
Thank you for the thorough explanation!
AndrewHowell
Moderator

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
Quartz | Level 8
This also works!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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