data have;
input id month1-month12;
cards;
1 0 0 1 1 1 1 0 0 0 0 0 0
2 0 0 1 1 1 1 1 1 1 0 0 0
3 1 1 1 1 1 1 1 0 0 0 0 0
4 0 0 0 0 0 0 0 0 0 0 0 0
5 1 1 1 1 1 1 1 1 1 1 1 1
;
run;
data want ;
set have ;
length str $12 ;
str = cats(of month1-month12);
first = index(str,'1');
last = first + indexc(cats(substr(str,first+1),'0'),'0') -1 ;
put (id first last str) (3*3. +1 $12.);
run;
1 3 6 001111000000
2 3 9 001111111000
3 1 7 111111100000
4 0 0 000000000000
5 1 12 111111111111
I think features of the FINDC function make this a little bit easier.
Thank you Tom, this does work for what I wanted. Unfortunately, the plot thickens. I am trying to construct episodes of coverage for patients in an insurance database. My episodes of coverage are continuous periods of time that a patient was enrolled in an insurance plan. This is what I have:
ID YEAR ENRIND1-ENRIND12 MEMDAYS1-MEMDAYS12
101 2008 0 0 1 1 1 1 1 1 1 1 1 1 0 0 31 30……………..31
101 2009 1 1 1 1 1 0 0 1 1 1 1 1 31 28…0 0……………31
101 2010 1 1 1 1 1 1 1 1 1 0 0 0 31 28………………0 0 0
What I want to make is two episodes of continuous coverage for this:
ID FIRST1 LAST1 FIRST2 LAST2 … FIRSTN LASTN
101 2/1/2008 5/31/2009 8/1/2009 9/30/2010
The MEMDAYS1-12 variables might be necessary to create the day for LAST1-N date variables. The FIRST1-N will always start with the first of the month.
Complications being that the duplications for IDs are necessary for other parts of the analysis and I think it will be quite fine to end up with something like this:
ID FIRST1 LAST1 FIRST2 LAST2 … FIRSTN LASTN
101 2/1/2008 5/31/2009 8/1/2009 9/30/2010
101 2/1/2008 5/31/2009 8/1/2009 9/30/2010
101 2/1/2008 5/31/2009 8/1/2009 9/30/2010
I added the FIRSTN LASTN because some people may have > 2 periods of continuous coverage.
Any assistance here is appreciated, I only wish I could repay in some way.
Thank you in advance.
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.
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.