topic Re: SAS Extracting month and year from a Date column with format MMDDYY10. in SAS Programming

SAS Extracting month and year from a Date column with format MMDDYY10.

Saba1 — Sun, 24 Sep 2017 02:33:19 GMT

I have a data set with "Date" column and the format is MMDDYY10. (e.g. 12/31/2010). I want to create another column of "Date_New" with only month and year like 201012. When I apply the following code, I get a column of values 196001 instead of required date format.

data want;
set have;

date_new = datepart(date);
FORMAT date_new yymmn6.;

run;

Please guide me in this regard. Thanks.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

SASKiwi — Sun, 24 Sep 2017 03:06:51 GMT

You ony need to apply the DATEPART function if your original date is a DATETIME. This should get you what you want:

data want;
set have;
date_new = date;
FORMAT date_new yymmn6.;
run;

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Saba1 — Sun, 24 Sep 2017 03:17:19 GMT

@SASKiwi That is great. Thanks.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Reeza — Sun, 24 Sep 2017 03:20:50 GMT

You don't need to create a new variable depending on what you need. You can summarize by the year month by using a format in a proc.

ie summarize the average open price by month in the STOCKS data set.

proc means data=sashelp.stocks mean;
class date;
format date yymmn6.;
var open;
run;

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Saba1 — Mon, 25 Sep 2017 05:06:56 GMT

@Reeza Thanks. But how about if one dataset has Date column in month format while other dataset has daily date format. Please suggest me some code so that they both are converted in similar format. I need to extract the values where both datasets' dates are matched.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Reeza — Mon, 25 Sep 2017 14:03:36 GMT

@Saba1 in this case, it's not formats that matter, it's the underlying data. That's an important distinction because formats only control what's displayed. To merge you need to make them the same, ie SAS dates, numeric or character, whichever is appropriate.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Saba1 — Fri, 29 Sep 2017 22:10:12 GMT

Thanks @Reeza for this useful Information. By the way I have used INTNX to convert daily date into monthly and then merged it easily with Monthly date dataset.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Reeza — Sat, 30 Sep 2017 02:12:06 GMT

@Saba1 wrote:

Thanks @Reeza for this useful Information. By the way I have used INTNX to convert daily date into monthly and then merged it easily with Monthly date dataset.

INTNX returns a date, it may be formatted to look like a month, but it's still a date variable. If the day portion is different the data will not merge correctly.

This is a key differentiation in how SAS handles data that is very important to understand.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Saba1 — Sat, 30 Sep 2017 03:05:40 GMT

@Reeza Actually in one of my dataset an event is occuring in different days of various months . The other dataset is monthly return. I need to convert those "daily dates" of events into mothly (month end) and then match with monthly returns whereever the dates are similar. In this way I analyse the effect of event on return. Therefore I have used INTNX because I want to convert daily sates into monthly.

Re: SAS Extracting month and year from a Date column with format MMDDYY10.

Reeza — Tue, 25 Dec 2018 19:24:19 GMT

This is getting into semantics but I'll still clarify in case you run into this again. SAS doesn't have dates without a day value. So what you've done is aligned your dates to be the same, for example the beginning or end of the month to allow them to merge which is the correct method. But you have not actually converted a "daily" date into a "monthly" date. Hope that helps to clarify this and Merry Christmas.