topic Re: Grab the month and year of a date variable in SAS Programming

Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 17:14:47 GMT

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.

Re: Grab the month and year of a date variable

JoshB — Wed, 20 Jan 2016 17:20:01 GMT

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.

Re: Grab the month and year of a date variable

Astounding — Wed, 20 Jan 2016 17:32:05 GMT

You already know how to apply the DATEPART function:

d = datepart(dt_var);

Try applying the right format to the result:

format d monyy7.;

Re: Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 17:34:31 GMT

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?

Re: Grab the month and year of a date variable

data_null__ — Wed, 20 Jan 2016 17:43:19 GMT

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

Re: Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 17:45:39 GMT

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

Re: Grab the month and year of a date variable

ballardw — Wed, 20 Jan 2016 17:53:17 GMT

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.

Re: Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 17:56:39 GMT

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.

Re: Grab the month and year of a date variable

Reeza — Wed, 20 Jan 2016 18:04:04 GMT

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

Re: Grab the month and year of a date variable

Astounding — Wed, 20 Jan 2016 18:11:51 GMT

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.

Re: Grab the month and year of a date variable

Reeza — Wed, 20 Jan 2016 18:20:21 GMT

Here's a sample that demonstrates the issue:

data sample;
date = dhms(today(), 11, 15, 0); /*Datetime variable*/
dtdate =datepart(date); /*Date variable*/

put date = datetime21.;
put date = dtdate9.;
put date = dtmonyy7.;
put dtdate = date9.;
put dtdate = monyy7.;
run;

And the output:

date=20JAN2016:11:15:00
date=20JAN2016
date=JAN2016
dtdate=20JAN2016
dtdate=JAN2016
NOTE: The data set WORK.SAMPLE has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

Re: Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 18:35:51 GMT

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?

Re: Grab the month and year of a date variable

Astounding — Wed, 20 Jan 2016 18:44:04 GMT

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.

Re: Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 18:50:07 GMT

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.

Re: Grab the month and year of a date variable

Reeza — Wed, 20 Jan 2016 18:52:33 GMT

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.

Re: Grab the month and year of a date variable

Astounding — Wed, 20 Jan 2016 18:53:56 GMT

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.

Re: Grab the month and year of a date variable

Steelers_In_DC — Wed, 20 Jan 2016 19:09:09 GMT

You don't specify if you want the output to be a date or character. Here is a solution for both:

data have;
infile cards dsd;
informat date1 date9.;
format date1 date9.;
input date1;
cards;
31Dec2015
;run;

data want;
set have;
date2 = date1;
format date2 monyy7.;
date3=input(put(date2,monyy7.),$7.);
run;

Re: Grab the month and year of a date variable

apolitical — Wed, 20 Jan 2016 21:33:50 GMT

Thank you all for helping out. It turns out proc sql has to interpret a date variable by the specific day, no matter how hard you try to hide it by using format. The solution is either to use proc means to get aggregates, or turn date variable into character variables. The former proved to be more natural, as it retains the chronological order of months.

Thank you so much.

Re: Grab the month and year of a date variable

ballardw — Wed, 20 Jan 2016 21:45:18 GMT

Or possibly leave the datetime value alone and use a format like DTMONYY7. when you need to use the date time grouped at month level.