Solved
Contributor
Posts: 40

# break SCD2 values into single years

[ Edited ]

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

Accepted Solutions
Solution
‎01-31-2018 02:32 AM
Super User
Posts: 2,068

## Re: break SCD2 values into single years

``````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;
``````

All Replies
Solution
‎01-31-2018 02:32 AM
Super User
Posts: 2,068

## Re: break SCD2 values into single years

``````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;
``````
Contributor
Posts: 40