topic Convert year and month columns to a date in one column in SAS Programming

Convert year and month columns to a date in one column

ywon111 — Thu, 03 Feb 2022 02:38:01 GMT

Here is what I have

data have;
input year month;
infile datalines missover;
datalines;
2020 1
2020 2
2020 3
;

Keen to want below:

31Jan2020

29Feb2020

31Mar2020

Thanks in advance

Re: Convert year and month columns to a date in one column

Reeza — Thu, 03 Feb 2022 02:46:03 GMT

data want;
set have;
date = mdy(month, 1, year);
date_month_end = intnx('month', date, 0, 'e');
format date: date9.;
run;

@ywon111 wrote:

Here is what I have

data have;
input year month;
infile datalines missover;
datalines;
2020 1
2020 2
2020 3
;

Keen to want below:

31Jan2020

29Feb2020

31Mar2020

Thanks in advance

Re: Convert year and month columns to a date in one column

japelin — Thu, 03 Feb 2022 02:46:12 GMT

how about this

data want;
  set have;
  dmy=intnx ('month',mdy(month,1,year),0,'E');
  format dmy date9.;
run;

Re: Convert year and month columns to a date in one column

ballardw — Thu, 03 Feb 2022 02:46:54 GMT

One way:

data have;
input year month;
infile datalines missover;
dateval = intnx('month',mdy(month,1,year),0,'E');
format dateval date9.;
datalines;
2020 1
2020 2
2020 3
;

The MDY function uses month, day and year values to create a date, in this case using 1 for day as every month has a day 1. The INTNX function then adjusts that date to the end of that current month, 'month' being the interval, 0 being the current month to adjust the date value and 'E' to set the end of the interval.