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

Hi Folks,

 

I have a piece of code that I use (below) to default missing days from partial dates to the first of the month when appropriate but in this case I need to default the missing days to the end of the month, which seems more complicated to a novice like me since the last day of the month can vary. Any ideas how to proceed?

 

/* month and year entered, default to start of month */

if substr(eoslvdat,1,1) = '-' and substr(eoslvdat,3,1) ne '-' then eoslvdtn=input(trim('01')||substr(eoslvdat,3),date9.)

 

 

data WORK.EOS;
  infile datalines dsd truncover;
  input _usubjid:$6. eoslvdat:$9.;
  label _usubjid="Unique Subject Identifier" eoslvdat="Date of last visit";
datalines;
01/001 25APR2019
01/002 02MAY2019
01/003 08OCT2018
01/004 15NOV2018
01/005 12JUN2018
01/006 06JUN2019
01/007 --DEC2018
;;;;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Convert them to SAS dates and then use INTNX to set to the end of the month.

1. Use TRANSLATE to convert the -- to 01
I'll leave that to you. Do you have more than missing days you need to account for? You can use the code you already have to put it as 01.
2. Convert to SAS date using INPUT()
new_var = input(eoslvdat, date9.);
3. Change to end of the month
new_var = intnx('month', new_var, 0, 'e');

View solution in original post

4 REPLIES 4
Reeza
Super User
Convert them to SAS dates and then use INTNX to set to the end of the month.

1. Use TRANSLATE to convert the -- to 01
I'll leave that to you. Do you have more than missing days you need to account for? You can use the code you already have to put it as 01.
2. Convert to SAS date using INPUT()
new_var = input(eoslvdat, date9.);
3. Change to end of the month
new_var = intnx('month', new_var, 0, 'e');
PSU_Sudzi
Obsidian | Level 7

Thanks for the response Reeza. In this dataset I only need to account for missing days, not months or years as I might in others. I hadn't thought of converting the -- to 01 and then using INTNX, but that seems like a good idea!

novinosrin
Tourmaline | Level 20


data WORK.EOS;
  infile datalines  dlm=' ' truncover;
  input _usubjid:$6. eoslvdat :$9.;
  label _usubjid="Unique Subject Identifier" eoslvdat="Date of last visit";
datalines;
01/001 25APR2019
01/002 02MAY2019
01/003 08OCT2018
01/004 15NOV2018
01/005 12JUN2018
01/006 06JUN2019
01/007   DEC2018
;;;;

data want;
set eos;
want=input(eoslvdat,?? date9.);
if want=. then want=intnx('mon',input(cats('01',eoslvdat),date9.),0,'e');
format want date9.;
run;

And suppose you have full missing values for your eoslvdat variable, the above is modified to

 

data want;
set eos;
if not missing(eoslvdat) then do;
want=input(eoslvdat,?? date9.);
if want=. then want=intnx('mon',input(cats('01',eoslvdat),date9.),0,'e');
end;
format want date9.;
run;

 

PSU_Sudzi
Obsidian | Level 7

Thank you Novinosrin, this also seems like it would work, will give it a shot too!

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!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2252 views
  • 0 likes
  • 3 in conversation