Grab the month and year of a date variable

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Grab the month and year of a date variable

Say we have a time variable, in date9, or datetime18. format. I would like to grab only the month and year part of the value and save it. For example, turn 31Dec2015 to Dec2015. datepart() function grabs the specific date of a time value, is there something similar to that but only grabs the month and year? Thanks.


Accepted Solutions
Solution
‎01-20-2016 04:29 PM
Super User
Posts: 5,516

Re: Grab the month and year of a date variable

Posted in reply to apolitical

Even though the underlying value changes day by day, most SAS procedures that summarize will respect the format and group by the formatted value instead of the underlying value.

To take things step by step and guarantee the result you want, create a new variable with the month and year.  After applying DATEPART once:

 

category = put(date_only, monyy7.);

 

Unfortunately, the values you get will not necessarily be in the order that you want.  You might want to try a format that contains the year followed by the month, like 1960-01 to keep the values in order.

View solution in original post


All Replies
Contributor
Posts: 56

Re: Grab the month and year of a date variable

Posted in reply to apolitical

SAS has functions for both year and month if you pass it a numeric date.

 

year(date) or

month(date)

 

These will be numeric results.

Contributor
Posts: 73

Re: Grab the month and year of a date variable

Thank you. They grab only the year or the month of a time value respectively, and save it as regular numeric format, not date format. Is there another function that grabs both the year and month of a certain time value, and preferably save it as a date format?

Super User
Posts: 5,516

Re: Grab the month and year of a date variable

Posted in reply to apolitical

You already know how to apply the DATEPART function:

 

d = datepart(dt_var);

 

Try applying the right format to the result:

 

format d monyy7.;

 

 

Respected Advisor
Posts: 3,799

Re: Grab the month and year of a date variable

Posted in reply to Astounding

With the DT version of that format no need for new variable.

2579  data _null_;
2580     x = datetime();
2581     put x=dtmonyy7.;
2582     run;

x=JAN2016
Contributor
Posts: 73

Re: Grab the month and year of a date variable

Posted in reply to data_null__
I did this:

data test;
set test;
put date=MONYY7.;
run;

However, the "date" variable has not changed from before, still shows the entire date and seems to be date9. format.
Super User
Posts: 19,865

Re: Grab the month and year of a date variable

Posted in reply to apolitical

Because you have a datetime variable use dtFORMAT name instead of just the format.

 

 

Contributor
Posts: 73

Re: Grab the month and year of a date variable

Posted in reply to Astounding

I tried it. I comes out to be always JAN1960, double click on it shows "1/1/1960". What may be causing this?

Super User
Posts: 11,343

Re: Grab the month and year of a date variable

Posted in reply to apolitical

SAS Date values are the number of days since 1/1/1960. If your "date" values show as 1/1/1960 then the way you created them was incorrect as they are resolving to something between 0 and 2. Print the data or change the display with a format like best10. and see what the values are then.

Super User
Posts: 5,516

Re: Grab the month and year of a date variable

Posted in reply to apolitical

Without looking over your shoulder and seeing the steps you applied, we're guessing.

 

Here's my guess.

 

You applied the datepart function an extra time.  It was already applied once, converting the datetime variable to a date.  Then you applied it a second time, converting the date to January 1, 1960.

 

Try printing the variable without a format and see what the actual value is.

 

Good luck.

Contributor
Posts: 73

Re: Grab the month and year of a date variable

Posted in reply to Astounding
that's indeed what i did. the raw data came in with date and time, i first did datetime() to get ride of the time part. later to aggregate over month, i want to make another variable capturing only the month and year.

i have tried keeping the raw date_time variable, and making 2 variables, one with date9, format, the other with monyy7. format, both using datepart(date_time) function, but they come out the same, both still containing the day of the month. so how would i fix this?
Super User
Posts: 5,516

Re: Grab the month and year of a date variable

Posted in reply to apolitical

While a number of the suggested approaches would work, I'll repeat my original idea.

 

After applying DATEPART only once, print the result using the MONYY7 format.

 

Good luck.

Contributor
Posts: 73

Re: Grab the month and year of a date variable

Posted in reply to Astounding
by doing that i can get the variable to show something like "DEC2015", but double click on it it will still show the day of the month, which is what i want to lose, so i can aggregate over all days of the same month. right now when i do that, it will distinguish between 1/1/2014 and 1/2/2014 and refuse to add them up, even though the variable made with monyy7. only shows Jan2014.
Super User
Posts: 19,865

Re: Grab the month and year of a date variable

Posted in reply to apolitical

That depends on the proc, PROC SQL will distinguish between the days, but PROC MEANS/FREQ will respect the format.

 

Also, your question initially stated you wanted to create a date variable. A date variable requires a day. 

 

I see a few options:

1. Convert all to character fields - dates will not sort properly in your tables (APR is first month to show)

2. Change all dates to either 1st or 15th of the month so they'll aggregate together.

3. Use procs above, which respect date format.

Solution
‎01-20-2016 04:29 PM
Super User
Posts: 5,516

Re: Grab the month and year of a date variable

Posted in reply to apolitical

Even though the underlying value changes day by day, most SAS procedures that summarize will respect the format and group by the formatted value instead of the underlying value.

To take things step by step and guarantee the result you want, create a new variable with the month and year.  After applying DATEPART once:

 

category = put(date_only, monyy7.);

 

Unfortunately, the values you get will not necessarily be in the order that you want.  You might want to try a format that contains the year followed by the month, like 1960-01 to keep the values in order.

☑ This topic is solved.

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

Discussion stats
  • 18 replies
  • 13981 views
  • 1 like
  • 7 in conversation