topic Converting concat char to concat date using monyy5. in SAS Procedures

Converting concat char to concat date using monyy5.

Q1983 — Thu, 26 Nov 2015 17:17:54 GMT

I have a field called reporting_dt_bin shown as a character. It is a concatenate of reporting_Yr and reporting month, both of which were originally dates

Reporting_dt_Bin = (Reporting_year_Bin || '-' || Reporting_month_txt); produces this output example

Reporting_dt_Bin

15- 3

15- 4

15- 5

15- 6

15- 7

Is there a way to maintain the reporting_dt_bin format and create another format that converts this into a date

Example

Reporting_dt_bin2

Mar15

Apr15

May15

The date format would be monyy5.

Re: Converting concat char to concat date using monyy5.

FreelanceReinh — Thu, 26 Nov 2015 18:14:30 GMT

There may be more elegant solutions, but this should do what you want, if the year and month variables are still available:

data have;
length Reporting_dt_bin $5;
Reporting_year_Bin='15';
do m=3 to 7;
  Reporting_month_txt=put(m,2.);
  Reporting_dt_Bin = (Reporting_year_Bin || '-' || Reporting_month_txt);
  output;
end;
drop m;
run;

proc print width=min;
run;


data want;
set have;
length Reporting_dt_bin2 $5;
Reporting_dt_bin2=propcase(put(input(cats(Reporting_year_Bin,put(input(Reporting_month_txt,2.),z2.)), yymmn4.),monyy5.));
run;

proc print width=min;
run;

If only Reporting_dt_Bin is available, you could resort to this:

data have;
length Reporting_dt_bin $5;
do m=3 to 7;
  Reporting_dt_bin='15-'||put(m,2.);
  output;
end;
drop m;
run;

proc print width=min;
run;


data want;
set have;
length Reporting_dt_bin2 $5;
Reporting_dt_bin2=propcase(put(input(cats(scan(Reporting_dt_bin,1,'-'),put(input(scan(Reporting_dt_bin,2,'-'),2.),z2.)), yymmn4.),monyy5.));
run;

proc print width=min;
run;

If you are happy with MAR15 etc. (this is actually MONYY5. format) instead of Mar15, you can omit PROPCASE.

Re: Converting concat char to concat date using monyy5.

Steelers_In_DC — Fri, 27 Nov 2015 18:14:02 GMT

Here is another solution:

data have;
infile cards dsd;
input Reporting_dt_Bin$;
cards;
15- 3
15- 4
15- 5
15- 6
15- 7
;

proc format;
   value mon 1 = JAN
              2 = FEB
              3 = MAR
              4 = APR
              5 = MAY
              6 = JUN
              7 = JUL
              8 = AUG
              9 = SEP
              10= OCT
              11= NOV
              12= DEC
;

data want;
set have;
Reporting_dt_Bin2 = input(cats(scan(Reporting_dt_Bin,1,'-'),put(input(scan(Reporting_dt_Bin,2,'-'),8.),mon.),input(put(year(today()),4.),$4.)),date9.);
format Reporting_dt_Bin2 monyy5.;
run;

This takes the character input you have, breaks it up and formats it as DDMMMYYYY. The year is based on today().

Re: Converting concat char to concat date using monyy5.

Patrick — Sat, 28 Nov 2015 02:33:08 GMT

And another coding option:

%let rx='s/(\d+)[^\d]+(\d+).*/\1-\2-01/';

data sample;
  input Reporting_dt_Bin $5.;
  format Reporting_dt monyy5.;
  Reporting_dt=input(prxchange(&rx,1,Reporting_dt_Bin),yymmdd8.);
  datalines;
5-10
5- 3
15-10
15- 3
15- 4
15- 5
15- 6
15- 7
;
run;

Re: Converting concat char to concat date using monyy5.

Tom — Sat, 28 Nov 2015 16:14:40 GMT

SAS does not have an INFORMAT for converting values in YYMM (or YY-MM) format to dates. But if you add an '-1' to the end you can use the YYMMDD informat to convert that string to a date. You could then attach the MONYY format to it. Or use the MONYY format to convert it to a text string. Also note that the YYMMDD format does not like the space after the hyphen in the example strings you posted. You can use the COMPRESS() function to remove the spaces.

data have;
  input reporting_dt_bin $5.;
  list;
cards;
15- 3
15- 4
15- 5
15- 6
15- 7
;
data want ;
   set have;
   date=input(cats(compress(reporting_dt_bin,' '),'-01'),yymmdd8.);
   format date monyy5.;
run;

Note that you could generate the REPORTING_DT_BIN without the spaces if you used:

Reporting_dt_Bin = catx('-',Reporting_year_Bin,Reporting_month_txt);

If you had the month as a number you could use the Z2 format to build values that have the leading zero when month is less than 10. This would have the advantage of both working with the YYMMDD informat and also allowing REPORTING_DT_BIN text string to sort properly.

Reporting_dt_Bin = catx('-',Reporting_year_Bin,put(input(Reporting_month_txt,2.),Z2.));

Re: Converting concat char to concat date using monyy5.

Patrick — Sun, 29 Nov 2015 00:17:09 GMT

@Tom "SAS does not have an INFORMAT for converting values in YYMM (or YY-MM) format to dates"

Actually there is informat YYMMN which can process such strings (not with a dash though but blanks as separator seem to work).

I prefere the solution you've posted so below just for "completeness":

data sample;
  input Reporting_dt_Bin $5.;
  format Reporting_dt monyy5.;
  Reporting_dt=input(compbl(translate(Reporting_dt_Bin,' ','- ')),yymmn.);
  datalines;
5-10
5- 3
15-10
15- 3
15- 4
15- 5
15- 6
15- 7
;
run;