How to combine year and month columns into one column

[Denali]

Hi,

I have a column that has year and another column has month. How do I combine these two columns and get a YYYY/MM information in one column? 

ID           Year              Month          Date (this is what I want)

001         2006             08                2006/08

002         2008             12                2008/12

003         2012             07                2012/07

Thanks in advance!

[Reeza]

You could use CATX() 

want = catx('/', year, put(month, z2.));

Or you can create a SAS date and apply a format. Not sure if there's an exact format for what you want.

want = mdy(month, year, 1);
format want yymm6.;

---

@Denali wrote:

Hi,

I have a column that has year and another column has month. How do I combine these two columns and get a YYYY/MM information in one column? 

ID           Year              Month          Date (this is what I want)

001         2006             08                2006/08

002         2008             12                2008/12

003         2012             07                2012/07

Thanks in advance!

---

[Tom · Accepted solution]

If MONTH is character then CATX() is probably want you want.

Date_code = catx('/',year,month);

If MONTH is numeric then you will probably want to use PUT() to let you force the addition of the leading zero for Jan to Sept.

Date_code = catx('/',year,put(month,z2.));

If you want to make an actual DATE then you will need to pick a day of the month.  You could then apply a format that will display the date using just the year and month. 

So if your year and month variables are numeric then you could do.

date = mdy(month,1,year);
format date yymms7.;

[Denali]

Hi Tom, 

Thank you for your code. It worked well when both columns contain data. 

However, it will show missing if someone only has either year or month data. Is there a way to keep the information as mush as possible?

ID               YEAR             MONTH         Date (this is what I want)

001             2012                  08               08/2012

002             2000                  10               10/2000

003                                       06               06/0000

004             2010                  99               00/2010

005             2009                  88               00/2009

I used below code: 

Date = mdy(Month,1,Year);

format Date mmyys7.;

Note: "blank", "88" or "99" are "missing". In the Date variable, I simply wrote "00"  represents those do not have month info and "0000" for those without year info. Does anyone has better way to keep the missing info in the "Date" variable? 

Thanks in advance!

[Tom]

To store a DATE you need to store an actual date. There is no month 0 and SAS does not support dates as old as year 0 (which version of the calendar would it choose?).

If you want to store a date then you could use special missing for your other possible values.

if nmiss(month,year)=0 then date = mdy(Month,1,Year);
Else if missing(month) then date=.Y;
else if missing(year) then date=.M;
else date=.;
format Date mmyys7.;

Note that you would still need to keep the MONTH and YEAR variables to know which non-missing year the .Y value represents for a particular observation.

[Rishi16]

Hi Tom,

Thanks for sharing your code.

Can you please explain why 1 is needed in the below code:

date = mdy(month,1,year);
format date yymms7.;

Thanks in advance.

[Reeza]

SAS dates require all components to be filled, including the day portion. MDY = MonthDayYear, so the second parameter is the day which in this case is being hardcoded to the first of the month. But it's not shown in the final results so it can arbitrarily be set to almost anything. 1 is easy because every month has a 1st and it means the same thing. Mid month or end of the month would vary by month. 

---

@Rishi16 wrote:
Hi Tom,

Thanks for sharing your code.

Can you please explain why 1 is needed in the below code:

date = mdy(month,1,year);
format date yymms7.;

Thanks in advance.

---

[Rishi16]

Thanks a lot Reeza

[J_J_J]

Hi

When I use this code:

date = mdy(month,1,year);
format date yymms7.;

I get period like this 2021M01 not 2021/01.

What did i do wrong?

[Tom]

What is the difference?  Both are valid ways to display a date in the first month of the year 2021.

Most likely you changed the format attached to the variable from YYMMS to YYMM instead.

[Sirisha1520]

I have current date taking from DATE() and want to conert it to MMYYYY format. How do I combine these two? I tried substr and string but thats not working as expected?

ID Year Month Date (this is what I want)

001 2021 07 072021





Thanks in advance!

[Reeza]

You don't convert it to MMYYY format, you apply a format. This way you don't change the underlying value but only the presentation. Otherwise your data will also sort alphabetically instead of via the calendar.

FORMAT variableName MMYYN6.;