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

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

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

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.

 


Accepted Solutions
Solution
‎10-05-2017 06:00 PM
Super User
Posts: 3,481

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

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;

View solution in original post


All Replies
Solution
‎10-05-2017 06:00 PM
Super User
Posts: 3,481

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

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;
Contributor
Posts: 33

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

@SASKiwi That is great. Thanks.

Super User
Posts: 21,546

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

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;
Contributor
Posts: 33

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

[ Edited ]

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

Super User
Posts: 21,546

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

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

Contributor
Posts: 33

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

[ Edited ]

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.

Super User
Posts: 21,546

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


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. 

 

 

 

 

Contributor
Posts: 33

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

[ Edited ]

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

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 591 views
  • 3 likes
  • 3 in conversation