Converting concat char to concat date using monyy5.

[Q1983]

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.

[FreelanceReinh]

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.

[Steelers_In_DC]

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().

[Patrick]

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;

[Tom]

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

[Patrick]

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