break SCD2 values into single years

[FK1]

Hi Everyone,

can anybody help me with this:

data HAVE;
format ID $2. VAR1 $4. VAR2 8.;
format VALID_FROM VALID_TO  Date9.;
input     @1  ID  
        @3    VAR1  
        @8  VAR2  
        @10 VALID_FROM 
        @16 VALID_TO
;
datalines;
AB xxxx 1 17828 19272
;
run;


data WANT;
format ID $2. VAR1 $4. VAR2 8.;
format VALID_FROM VALID_TO  Date9.;
input     @1  ID  
        @3    VAR1  
        @8  VAR2  
        @10 VALID_FROM 
        @16 VALID_TO
;
datalines;
AB xxxx 1 17828 17897
AB xxxx 1 17898 18262
AB xxxx 1 18263 18627
AB xxxx 1 18628 18992
AB xxxx 1 18993 19272

;
run;

I want to have as much observations as are years in between the valid_from and valid_to date and simultaneously retain the values for the variables ID, VAR1, VAR2.

How can I program this in SAS BASE?

Thanks,

FK1

[novinosrin · Accepted solution]

data HAVE;
format ID $2. VAR1 $4. VAR2 8.;
format VALID_FROM VALID_TO  Date9.;
input 	@1  ID  
		@3	VAR1  
		@8  VAR2  
		@10 VALID_FROM 
		@16 VALID_TO
;
datalines;
AB xxxx 1 17828 19272
run;

data want;
set have;
by id;
_VALID_TO=VALID_TO;
_year=year(VALID_TO)-year(VALID_from);
do _n=year(VALID_from) to year(_VALID_TO);
	if _n=year(VALID_from) then VALID_TO=intnx('year',VALID_from,0,'end');
	else if year(VALID_from)<_n<year(_VALID_TO) then do; VALID_from=mdy(1,1,_n);VALID_TO=intnx('year',mdy(1,1,_n),0,'end');end;
	else if _n=year(_VALID_TO) then do;VALID_from=mdy(1,1,_n);VALID_TO=_VALID_TO;end;
	output;
end;
format _VALID_TO date9.;
drop _:;
run;
	

[FK1]

Thanks, novinosrin! It works perfectly fine.