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

[Saba1]

Hi

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.

[SASKiwi · Accepted solution]

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;

[Saba1]

@SASKiwi That is great. Thanks.

[Reeza]

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;

[Saba1]

@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. 

[Reeza]

@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. 

[Saba1]

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.

[Reeza]

---

@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. 

[Saba1]

@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. 

[Reeza]

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.