DATA Step, Macro, Functions and more

break SCD2 values into single years

Accepted Solution Solved
Reply
Contributor FK1
Contributor
Posts: 40
Accepted Solution

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;
	

View solution in original post


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

Re: break SCD2 values into single years

Posted in reply to novinosrin
Thanks, novinosrin! It works perfectly fine.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 127 views
  • 0 likes
  • 2 in conversation