BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Estelle
Calcite | Level 5

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
ballardw
Super User
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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Super User Tom
Super User

@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
Opal | Level 21

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
Calcite | Level 5

Right! Put statements.

 

They put stuff in the log, I knew that!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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