BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Saba1
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

9 REPLIES 9
SASKiwi
PROC Star

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;
Reeza
Super User

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
Quartz | Level 8

@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
Super User

@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
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 50907 views
  • 5 likes
  • 3 in conversation