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
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: