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

Hi there,

I'm struggling trying to build an array m1-m12, which is meant to count the number of days in each month for each effective record.

I have the two columns "Effective" and "Expiry" and m1-m12 is what I'd like to construct.

EffectiveExpirym1m2m3m4m5m6m7m8m9m10m11m12
03MAR201121MAR20110019000000000
22MAR201116JUN20110010303116000000
17JUN201130SEP20110000014313130000

I'd appreciate any help, I tried to build another two arrays picking up beginning and end dates, but couldn't "see" the solution so to speak. I feel like this should be pretty easy, but it's been pretty elusive to me so far.

Cheers

JP

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

One way:

array m {12};

do i=month(effective) to month(expiry);

    m{i} = 1 +  min(expiry, intnx('month', effective, i-month(effective), 'end')) -

                      max(effective, intnx('month', effective, i-month(effective)));

end;

That fills in the non-zero months, and I'm assuming you can fill in the rest easily enough.

I hope I got it right because my head hurts after that one. 

View solution in original post

7 REPLIES 7
ballardw
Super User

Will the dates every cross a year boundary? Such as Effective=08NOV2011 and Expiry=28MAY2012.

Can the Expiry be more than a year greater than Effective? Such as Effective=08NOV2011 and Expiry= 05FEB2013.

If so, what should M1-M12 look like?

jp
Fluorite | Level 6 jp
Fluorite | Level 6

ballardw wrote:

Will the dates every cross a year boundary? Such as Effective=08NOV2011 and Expiry=28MAY2012.

Can the Expiry be more than a year greater than Effective? Such as Effective=08NOV2011 and Expiry= 05FEB2013.

If so, what should M1-M12 look like?

Effective and Expiry dates are always constrained to be in the same calendar year. So the minimum effective date would always 01JAN201X and maximum Expiry date would be 31DEC201X.

Astounding
PROC Star

One way:

array m {12};

do i=month(effective) to month(expiry);

    m{i} = 1 +  min(expiry, intnx('month', effective, i-month(effective), 'end')) -

                      max(effective, intnx('month', effective, i-month(effective)));

end;

That fills in the non-zero months, and I'm assuming you can fill in the rest easily enough.

I hope I got it right because my head hurts after that one. 

jp
Fluorite | Level 6 jp
Fluorite | Level 6

Thanks Astounding, that's working perfectly as far as I can see.

Vortex
Calcite | Level 5

Hi I was wondering if there is  code that does similar to the code Astounding provided but it can cross year boundaries such as December 13, 2014 to January 12, 2015.

Thank you

ballardw
Super User

Vortex   you would be better off starting a new thread with more specific examples of what you are starting from and what the result should be. There are lots of "similar" codes possible but whether any of them would address your issue without details is problematic.

Provide an example data set with the key elements, best is as a data step with datalines and an input statement so we can execute it and manipulate a standard set of data.

Then an example of what the output should look like.

Also if dealing with date data it is import to know if the existing values are SAS date valued variables or character.

If this involves and arbitrary number of months, where the number of months and start/end change for different records, then it may be appropriate to discuss whether attempting to build a data set at the beginning is a good idea at all.

Ksharp
Super User

Here is one way:

data have;
input (effective expiry) (: date9.);
format effective expiry date9.;
cards;
03MAR2011     21MAR2011
22MAR2011     16JUN2011
17JUN2011     30SEP2011
17DEC2011     10FEB2012
;
run;
data temp;
 set have;
 n+1;
 do date=effective to expiry;
  output;
 end;
 format date date9.;
 keep n date;
run;
proc summary data=temp nway;
 class n date;
 format date monyy7.;
 output out=temp1;
run;
proc transpose data=temp1 out=want(drop=_:);
 by n ;
 var _FREQ_;
 id date;
run;

Xia Keshan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2207 views
  • 1 like
  • 5 in conversation