Convert Char 6. to Date9. (to beginning and end of month)

[Estelle]

SAS Hive Mind:

I have a 6 length character variable, yrmo, that represents a date like '201701'.

I would like to create two variables from yrmo in sasdate9. formats:

Beg_date_cov

eff_date_cov

Where beg_date_cov would start at the first of the month like: '01JAN20167'

And end_date_cov would be the last day of the month like: '31JAN2017'

I have tried numerous iterations of code involving intnx, put, input, format, informat, 'b', 'e': I won't bother writing all iterations that I've tried here since there many and well, they're all wrong. 

Thoughts?

[ballardw]

data example;
   x='201701';
   xdate = input(x,yymmn6.);
   put xdate date9.; /* note that SAS Assigns first of month by default*/
   endofmonth= intnx('month',xdate,0,'E');
   put endofmonth date9.;
run;

add format statements if you want them permanently associated with the variables. Look in the log for the result of the puts if you don't use them much.

[Estelle]

I must be missing something. I am getting nulls as (.) for yrmo, beg_cov_dt, and end_cov_dt., using this:

libname TO '/sasdata3/MI/projects/valuequest/production/output';

Data TO.mbrs_dt_test;
set TO.mbrshp_0716to0617_final_test;
YRMO = YRMO;
BEG_COV_DT = INPUT(YRMO,YYMMN6.);
PUT BEG_COV_DT DATE9.;
END_COV_DT = INTNX('MONTH',BEG_COV_DT,0,'E');
PUT END_COV_DT DATE9.;
RUN;

(I assume you meant to set YRMO equal to itself?)

[art297 · Accepted solution]

Are you sure it's a character field. Here is your example presented two ways: first assuming it is a character field, then assuming it's a numeric field:

libname to '/folders/myfolders';
data to.mbrshp_0716to0617_final_test;
  input yrmo $;
  cards;
201702
201605
;

Data TO.mbrs_dt_test;
  set TO.mbrshp_0716to0617_final_test;
  format BEG_COV_DT END_COV_DT date9.;
  BEG_COV_DT = INPUT(YRMO,YYMMN6.);
  PUT BEG_COV_DT;
  END_COV_DT = INTNX('MONTH',BEG_COV_DT,0,'E');
  PUT END_COV_DT;
RUN;

data to.mbrshp_0716to0617_final_test;
  input yrmo;
  cards;
201702
201605
;

Data TO.mbrs_dt_test;
  set TO.mbrshp_0716to0617_final_test;
  format BEG_COV_DT END_COV_DT date9.;
  BEG_COV_DT = INPUT(put(YRMO,6.),YYMMN6.);
  PUT BEG_COV_DT;
  END_COV_DT = INTNX('MONTH',BEG_COV_DT,0,'E');
  PUT END_COV_DT;
RUN;

Art, CEO, AnalystFinder.com

[Estelle]

It was a character. I am not sure what I was doing wrong but it does work now.

I have adopted a new problem with this solution, it now write all of these dates to the log. It's about 65 million rows so the log gets too big to open, I can only see it in unix. 

Any idea on how an option to suppress that? I can probably look around until I find some option to make that happen... or I will just post another issue here in SAS communities 🙂 

[Tom]

---

@Estelle wrote:

I must be missing something. I am getting nulls as (.) for yrmo, beg_cov_dt, and end_cov_dt., using this:

libname TO '/sasdata3/MI/projects/valuequest/production/output';

Data TO.mbrs_dt_test;
set TO.mbrshp_0716to0617_final_test;
YRMO = YRMO;
BEG_COV_DT = INPUT(YRMO,YYMMN6.);
PUT BEG_COV_DT DATE9.;
END_COV_DT = INTNX('MONTH',BEG_COV_DT,0,'E');
PUT END_COV_DT DATE9.;
RUN;

(I assume you meant to set YRMO equal to itself?)

---

Setting a variable to itself will not do anything.

I suspect that YRMO is a numeric variable instead of character string. So you need to tell SAS how to convert it to character strings for the INPUT() function to work on.  Otherwise it will use BEST12. format to convert it to something like '      201706' and then when you read just the first 6 characters all you are reading are spaces.

BEG_COV_DT = INPUT(put(YRMO,Z6.),YYMMN6.);

[art297]

You're getting all of those date written to the log because of the two put statements you have in your code. Just remove them!

Art, CEO, AnalystFinder.com

[Estelle]

Right! Put statements.

They put stuff in the log, I knew that!