Grab the month and year of a date variable

[apolitical]

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.

[JoshB]

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.

[apolitical]

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?

[Astounding]

You already know how to apply the DATEPART function:

d = datepart(dt_var);

Try applying the right format to the result:

format d monyy7.;

[data_null__]

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

[apolitical]

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.

[Reeza]

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

[apolitical]

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

[ballardw]

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.

[Astounding]

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.

[apolitical]

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?

[Astounding]

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.

[apolitical]

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.

[Reeza]

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.

[Astounding · Accepted solution]

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.