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!
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;
Are the number of observations for each ID in the two data sets always equal? What if have contains 4 observations for ID=11?
No that may differ.
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?
That should be added. Nothing will left out. I edited the above sample. Kindly go through it once again.
Thanks!
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;
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.