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!
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.;
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!
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.;
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!
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.
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.
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?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: