## how to change the data 1 to data 2 below?

Solved
Occasional Contributor
Posts: 10

# how to change the data 1 to data 2 below?

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

subject        visit                 days            inr

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

Accepted Solutions
Solution
‎04-06-2015 09:27 PM
Posts: 1,147

## Re: how to change the data 1 to data 2 below?

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

Thanks,
Jag

All Replies
Solution
‎04-06-2015 09:27 PM
Posts: 1,147

## Re: how to change the data 1 to data 2 below?

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

Thanks,
Jag
Occasional Contributor
Posts: 10

Susie

Super User
Posts: 10,761

## Re: how to change the data 1 to data 2 below?

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

Occasional Contributor
Posts: 10

## Re: how to change the data 1 to data 2 below?

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

Super User
Posts: 13,498

## Re: how to change the data 1 to data 2 below?

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.

Occasional Contributor
Posts: 10

## Re: how to change the data 1 to data 2 below?

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.

Super User
Posts: 10,761

## Re: how to change the data 1 to data 2 below?

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

Occasional Contributor
Posts: 10

## Re: how to change the data 1 to data 2 below?

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

Super User
Posts: 10,761

## Re: how to change the data 1 to data 2 below?

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

Occasional Contributor
Posts: 10

## Re: how to change the data 1 to data 2 below?

Thank you so much!

Susie

Super User
Posts: 10,761

## Re: how to change the data 1 to data 2 below?

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

Occasional Contributor
Posts: 10

## Re: how to change the data 1 to data 2 below?

I started a new run and it worked. Thank you!

Susie

Occasional Contributor
Posts: 10

## Re: how to change the data 1 to data 2 below?

I just selected the data with days of 0, 7, 14 ~91 and solved the problem. Thanks everyone!

Susie

🔒 This topic is solved and locked.