BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag

View solution in original post

13 REPLIES 13
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

Susie

Ksharp
Super User
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

IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

ballardw
Super User

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.

IDM
Calcite | Level 5 IDM
Calcite | Level 5

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.

Ksharp
Super User

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

IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

Ksharp
Super User

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

IDM
Calcite | Level 5 IDM
Calcite | Level 5

Thank you so much!

Susie

Ksharp
Super User

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

IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

Susie

IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

Susie

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1175 views
  • 5 likes
  • 4 in conversation