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?
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
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.
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?)
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
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 🙂
@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.);
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
Right! Put statements.
They put stuff in the log, I knew that!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: