Convert char format to

[Scott86]

Hi everyone,

I am trying to convert a char variable which is character variable $7. format (it looks like '2017-03' in the dataset). I am trying to concert it to a date format but it is not working. The code I am doing is below.

format payment_date YYMON7.;
payment_date = input(period_date,$7.);

I am not getting an error the payment_date field is a date format with a . in it.

Any help is appreciated

Thanks

[PeterClemmensen]

Try this

data _null_;
   dt_char = '2017-03';
   dt_num  = input(dt_char, ANYDTDTE.);
   put dt_num= date9.;
run;

[Reeza]

Your informat needs to align with the format the date should be read in. If you want it read in as a date, you need a date informat, not a character informat. If you remove the dash, I believe that's a YYMON7 input format or you can use anydtdte which guesses at the format.

[Tom]

---

@Scott86 wrote:

Hi everyone,

I am trying to convert a char variable which is character variable $7. format (it looks like '2017-03' in the dataset). I am trying to concert it to a date format but it is not working. The code I am doing is below.

format payment_date YYMON7.;
payment_date = input(period_date,$7.);

I am not getting an error the payment_date field is a date format with a . in it.

Any help is appreciated

Thanks

---

The TYPE of values that INPUT returns is based on the INFORMAT specification you use in the second argument.  Since you used a character informat it returned a character value.  Reading a character value with the $7. informat does nothing other than truncate it to the first 7 bytes.   You can see this by the note in the log saying it it tried to convert the output of the INPUT() function to a number to store into your new numeric (date) variable.

2477  data _null_;
2478    period_date='2019-07';
2479    format payment_date YYMON7.;
2480    payment_date = input(period_date,$7.);
2481  run;
NOTE: ....+....0....+..^.0...+
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      2480:18
NOTE: Invalid numeric data, '2019-07' , at line 2480 column 18.
period_date=2019-07 payment_date=. _ERROR_=1 _N_=1

That is a difficult string layout for SAS to convert to a date, but the ANYDTDTE informat should handle it.

2502  data _null_;
2503    period_date='2019-07';
2504    format payment_date YYMON7.;
2505    payment_date = input(period_date,anydtdte.);
2506    put (_all_) (=);
2507  run;

period_date=2019-07 payment_date=2019JUL

[novinosrin]

Hi @Scott86   When your date is a YEAR-MONTH/YEARMONTH value, converting to a numeric standard SAS date will revert to the first of the month. And this makes sense to give the user a start value and from that point increment upto 28/29/30/31 days depending upon the number of days in a month.

One way to have greater control over the process is by 

1. Using the compress function in an expression and remove the embedded delimiter and read the char date using the YEARMONTH(YYMMN) informat. And then format the numeric SAS date with any format of your choice. Example,

/*Your sample*/
data have;
 char_date= '2017-03';
run;

data want;
 set have;
 want_date=input(compress(char_date,'-'),yymmn6.);
 format want_date date9.;
run;

2. Since we are certain, any informat as mentioned above would revert to 1st of the month, You could concatenate the char YEAR-MONTH with '01' day value and read with an appropriate date informat (yymmdd10. in your case) and of course format the numeric derived SAS date with a format of your choice.

/*Your sample*/
data have;
 char_date= '2017-03';
run;
data want;
 set have;
 want_date=input(catx('-',char_date,'01'),yymmdd10.);
 format want_date date9.;
run;

3.Alternatively, ANYDT series of informats are useful. However here are some very important considerations.

First, there is the matter of the additional processing needed. These informats go through a decision tree to determine how to translate every single value encountered; therefore, the amount of additional processing will increase with the number of times each "ANYDATE" informat has to be used. This would have a negligible impact on a small amount of data, but if you need to use them on big data, you might want to consider standardizing the representation of your date, time, and datetime text values beforehand and using the corresponding informat. Second, it is entirely possible that you would want to consider a nonstandard value erroneous and don't want SAS to decide what to do with it without you being able to inspect it first. Third, exceptions can occur, even when the use of an ANYDATE informat is warranted. While it is always a good idea to check all data that you are converting to SAS from another source and especially when you are converting dates, times, and datetime values, it is critical if you are using the ANYDT. informats.

[Astounding]

While you have gotten accurate suggestions, I think they skipped some basic concepts that you may need to digest.

Dates in SAS refer to a specific day, not to a year and month.  So if you have "2017-03" in your data, what specific day would you like to convert that into?  March 1, 2017?  The answer has nothing to do with knowing the SAS tools to make the conversion.  The answer starts with knowing what dates represent, and what you are trying to accomplish.