topic Re: how to change the data 1 to data 2 below? in SAS Procedures

how to change the data 1 to data 2 below?

IDM — Tue, 07 Apr 2015 00:43:24 GMT

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

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

Jagadishkatam — Tue, 07 Apr 2015 01:27:48 GMT

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

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

IDM — Tue, 07 Apr 2015 02:52:54 GMT

it works! Thank you so much for your prompt reply!

Susie

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

Ksharp — Tue, 07 Apr 2015 12:35:02 GMT

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

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

IDM — Tue, 07 Apr 2015 17:25:24 GMT

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

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

ballardw — Tue, 07 Apr 2015 18:07:32 GMT

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.

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

IDM — Tue, 07 Apr 2015 18:34:55 GMT

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.

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

IDM — Tue, 07 Apr 2015 19:36:10 GMT

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

Susie

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

IDM — Tue, 07 Apr 2015 21:46:09 GMT

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

Susie

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

Ksharp — Wed, 08 Apr 2015 13:15:38 GMT

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

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

IDM — Wed, 08 Apr 2015 13:44:50 GMT

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

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

Ksharp — Wed, 08 Apr 2015 14:45:51 GMT


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

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

IDM — Wed, 08 Apr 2015 14:59:36 GMT

Thank you so much!

Susie

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

Ksharp — Thu, 09 Apr 2015 12:30:32 GMT

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