DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

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?

 


Accepted Solutions
Solution
‎01-23-2018 07:21 PM
Super User
Posts: 8,213

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

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

 

View solution in original post


All Replies
Super User
Posts: 13,868

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

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.

 

Occasional Contributor
Posts: 10

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

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?)

Solution
‎01-23-2018 07:21 PM
Super User
Posts: 8,213

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

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

 

Occasional Contributor
Posts: 10

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

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 Smiley Happy 

Super User
Super User
Posts: 8,260

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


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.);
Super User
Posts: 8,213

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

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

 

Occasional Contributor
Posts: 10

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

Right! Put statements.

 

They put stuff in the log, I knew that!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 243 views
  • 1 like
  • 4 in conversation