Update the columns using loop

[Sathish_jammy]

Hi Experts,

Please have a look at the given sample dataset.

data Want;
input ID Visit_count Ht Wt;
cards;
11 1 . .
11 2 . .
11 3 . .
11 4 . .
11 5 . .
23 1 . .
23 2 . .
23 3 . .
23 4 . .
65 1 . .
65 2 . .
run;

data Have;
input Id date mmddyy10. Height weight;
format date mmddyy10.;
cards;
11 05/04/2000 156 98
11 04/06/2000 156 94
11 08/08/2001 157 92
23 07/06/2010 187 65
23 12/10/2010 187 68
23 04/05/2011 187 .
23 11/15/2012 187 70
65 10/07/2015 166 84
65 12/12/2016 167 86
;

I have to update the Ht and Wt in want table from have.

But I need to update it through order wise of date. The 1st date noted as VisitCount = 1, 2nd date noted as visitcount =2 and so on...

If the Height/Weight value is null on that Visit it remains null.

I could do it for a small dataset. But I have more than 500 visits for some IDs. I think update using the loop is the only solution.

I expect my OP as

ID	Visit_count	Height	weight
11	1	156	98
11	2	156	94
11	3	157	92
11	4	.	.
11	5	.	.
23	1	187	65
23	2	187	68
23	3	187	.
23	4	187	70
65	1	166	84
65	2	167	86

As well I need to update some other parameters from different tables using the same pattern.

Please help me to solve it.

Thanks in advance!

[PeterClemmensen]

Are the number of observations for each ID in the two data sets always equal? What if have contains 4 observations for ID=11?

[Sathish_jammy]

No that may differ.

[PeterClemmensen]

Ok. What if have contains 4 observations for ID=11? Should the fourth observation be added to the data set or should it be left out?

[Sathish_jammy]

That should be added. Nothing will left out. I edited the above sample. Kindly go through it once again.

Thanks!

[PeterClemmensen · Accepted solution]

Ok. Does this work for you?

data Have;
input Id date mmddyy10. Height weight;
format date mmddyy10.;
cards;
11 05/04/2000 156 98
11 04/06/2000 156 94
11 08/08/2001 157 92
23 07/06/2010 187 65
23 12/10/2010 187 68
23 04/05/2011 187 .
23 11/15/2012 187 70
65 10/07/2015 166 84
65 12/12/2016 167 86
;

data Temp;
   Visit_count=0;
   do until (last.Id);
      set Have;
      by Id;
      Visit_count=Visit_count+1;
      output;
   end;
   rename Height=Ht Weight=Wt;
run;

data Want;
input ID Visit_count Ht Wt;
cards;
11 1 . .
11 2 . .
11 3 . .
11 4 . .
11 5 . .
23 1 . .
23 2 . .
23 3 . .
23 4 . .
65 1 . .
65 2 . .
run;

data Want;
   update Want Temp updatemode=missingcheck;
   by ID Visit_count;
   drop Date;
run;

[Ksharp]

data Have;
input Id date mmddyy10. Height weight;
format date mmddyy10.;
cards;
11 05/04/2000 156 98
11 04/06/2000 156 94
11 08/08/2001 157 92
23 07/06/2010 187 65
23 12/10/2010 187 68
23 04/05/2011 187 .
23 11/15/2012 187 70
65 10/07/2015 166 84
65 12/12/2016 167 86
;


data Want;
input ID Visit_count Ht Wt;
cards;
11 1 . .
11 2 . .
11 3 . .
11 4 . .
11 5 . .
23 1 . .
23 2 . .
23 3 . .
23 4 . .
65 1 . .
65 2 . .
;
run;
data want;
ina=0;inb=0;
 merge want(in=ina drop=ht wt) have(in=inb drop=date);
 by id;
 if inb=0 then call missing(height,weight);
 if ina;
run;