the difference is to fit the gap of variable days, use the same data until a different days value come up.
Thank you for any help!
Susie
data1
subject visit days inr
1 1 0 1.4
1 2 3 1.5
1 3 7 1.6
2 1 0 1.5
2 2 5 1.7
data2
1 1 0 1.4
1 1 1 1.4
1 1 2 1.4
1 2 3 1.5
1 2 4 1.5
1 2 5 1.5
1 2 6 1.5
1 3 7 1.6
2 1 0 1.5
2 1 1 1.5
2 1 2 1.5
2 1 3 1.5
2 1 4 1.5
2 2 5 1.7
Please try
data have;
input subject visit days inr;
cards;
1 1 0 1.4
1 2 3 1.5
1 3 7 1.6
2 1 0 1.5
2 2 5 1.7
;
proc sort data=have;
by subject descending visit days;
run;
data want;
set have;
retain days2;
by subject descending visit days;
days2=lag(days)-1;
if days2=. or days2<0 then days2=days;
run;
proc sort data=want;
by subject visit days;
run;
data want2(rename=new=days);
set want;
do new=days to days2;
output;
end;
drop days days2;
run;
Thanks,
Jag
Please try
data have;
input subject visit days inr;
cards;
1 1 0 1.4
1 2 3 1.5
1 3 7 1.6
2 1 0 1.5
2 2 5 1.7
;
proc sort data=have;
by subject descending visit days;
run;
data want;
set have;
retain days2;
by subject descending visit days;
days2=lag(days)-1;
if days2=. or days2<0 then days2=days;
run;
proc sort data=want;
by subject visit days;
run;
data want2(rename=new=days);
set want;
do new=days to days2;
output;
end;
drop days days2;
run;
Thanks,
Jag
it works! Thank you so much for your prompt reply!
Susie
data have; input subject visit days inr; cards; 1 1 0 1.4 1 2 3 1.5 1 3 7 1.6 2 1 0 1.5 2 2 5 1.7 ; run; data want; merge have have(firstobs=2 keep=subject days rename=(subject=_subject days=_days)); output; if subject=_subject then do; do i=days+1 to _days-1; days=i;output; end; end; drop i _:; run;
Xia Keshan
The code worked in the example, but when applied to the real data which has lots of variables, it said
"ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid". Any suggestion?
BTW, I also need to change data1 to another data, data 3, create a new variable time, which is 0, 7, 14. 21, to 91. Then find the lab values that are the closest to but not greater than each of the time point. Would you please take a look? Thank you!
Susie
data1
subject visit days inr
1 1 0 1.4
1 2 3 1.5
1 3 6 1.6
1 4 11 1.4
1 5 13 1.7
2 1 0 1.5
2 2 5 1.7
data3
subject visit days inr time
1 1 0 1.4 0
1 3 6 1.6 7
1 5 13 1.7 14
2 1 0 1.5 0
2 2 5 1.7 7
With your initial requirement we were inserting days. At which value of days does your "time" (REAL bad variable name in my opinion) change from 0 to 7?
It may be that one approach is:
Time = round(days,7);
There errors you'll have to look at values of your by variable or your values in a the DO loop are missing.
You'll have to look at your data for that.
Thank you for the reply!
Now I need to change data 1 to a different dataset. I need to keep observations of days of 0, 7, 14, 21, all the way to 91(name the new variable DAYS2 if not TIME). it is not about rounding of days, I have to use the lab values (INR) in DAYS closest to but not greater than DAYS2 for each DAYS2(0, 7, 14, 21, ~ 91) in the new dataset. For example, the lab values for days2 7 are the values on days 7. If there were no lab values on days 7, chose lab values on days 6. If there were no lab values on days 6, chose lab values on days 5, and so son. Example of the new dataset is given as data3.
The ERROR told you there are some missing value in variable DAYS .
data want; merge have have(firstobs=2 keep=subject days rename=(subject=_subject days=_days)); output; if subject=_subject then do; if not missing(days) then do; do i=days+1 to _days-1; days=i;output; end; end; end; drop i _:; run;
I don't understand what your new question mean . Why would be 0,4 ,7 ? Is there some logic ?
Xia Keshan
it is 0, 7, 14, ....91. which is the result of each week. I got the new set by selecting DAYS in data 2. but how to choose DAYS till 182 or more, or how to choose a value if it is integer (I can divided by 7 to make a new variable WEEKS)?
Susie
data data1 ; input subject visit days inr ; cards; 1 1 0 1.4 1 2 3 1.5 1 3 6 1.6 1 4 11 1.4 1 5 13 1.7 2 1 0 1.5 2 2 5 1.7 ; run; %let weeks=3; data week; do i=0 to &weeks; days=7*i;output; end; drop i; run; proc sql; create table key as select * from (select distinct subject from data1),week order by subject,days; quit; data want; set data1 key(in=inb); by subject days; v=lag(visit);d=lag(days);i=lag(inr); if inb and not lag(inb) then do;time=days;days=.;output;end; drop visit days inr; run;
Xia Keshan
Thank you so much!
Susie
I just realize there is an underlining problem in my code. Sorry.
data data1 ;
input subject visit days inr ;
cards;
1 1 0 1.4
1 2 3 1.5
1 3 6 1.6
1 4 11 1.4
1 5 13 1.7
1 6 28 1.8
2 1 2 1.5
2 2 5 1.7
;
run;
%let weeks=3;
data week;
do i=0 to &weeks;
days=7*i;output;
end;
drop i;
run;
proc sql;
create table key as
select * from (select distinct subject from data1),week
order by subject,days;
quit;
data want;
set data1 key(in=inb);
by subject days;
v=lag(visit);d=lag(days);i=lag(inr);
if inb and not lag(inb) and subject=lag(subject) then do;time=days;output;end;
drop visit days inr;
run;
Xia Keshan
I started a new run and it worked. Thank you!
Susie
I just selected the data with days of 0, 7, 14 ~91 and solved the problem. Thanks everyone!
Susie
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.