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