topic Re: How to combine year and month columns into one column in SAS Programming

How to combine year and month columns into one column

Denali — Fri, 15 Feb 2019 20:36:26 GMT

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!

Re: How to combine year and month columns into one column

Reeza — Fri, 15 Feb 2019 20:40:37 GMT

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!

Re: How to combine year and month columns into one column

Tom — Fri, 15 Feb 2019 20:43:09 GMT

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

Re: How to combine year and month columns into one column

Denali — Mon, 18 Feb 2019 19:49:45 GMT

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!

Re: How to combine year and month columns into one column

Tom — Mon, 18 Feb 2019 19:57:40 GMT

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.

Re: How to combine year and month columns into one column

Rishi16 — Mon, 06 May 2019 22:03:53 GMT

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.

Re: How to combine year and month columns into one column

Reeza — Mon, 06 May 2019 22:47:46 GMT

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.

Re: How to combine year and month columns into one column

Rishi16 — Mon, 06 May 2019 23:44:48 GMT

Thanks a lot Reeza

Re: How to combine year and month columns into one column

J_J_J — Fri, 02 Jul 2021 08:09:25 GMT

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?

Re: How to combine year and month columns into one column

Tom — Fri, 02 Jul 2021 11:55:57 GMT

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.

Re: How to combine year and month columns into one column

Sirisha1520 — Wed, 21 Jul 2021 13:42:31 GMT

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!

Re: How to combine year and month columns into one column

Reeza — Wed, 21 Jul 2021 17:31:48 GMT

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