topic Re: Need a unique visitnum after merging multiple records in New SAS User https://communities.sas.com/t5/New-SAS-User/Need-a-unique-visitnum-after-merging-multiple-records/m-p/588104#M14765 <P>Posting data in usable form and showing the expected result ist always useful, if you expect a tested program as answer. You should also explain the role of "maxofdat1dat2".</P> Thu, 12 Sep 2019 05:28:36 GMT andreas_lds 2019-09-12T05:28:36Z Need a unique visitnum after merging multiple records https://communities.sas.com/t5/New-SAS-User/Need-a-unique-visitnum-after-merging-multiple-records/m-p/587899#M14751 <P>Hi all,&nbsp;</P><P>&nbsp;</P><P>below is my sample data for two data sets:</P><P>&nbsp;</P><P>data A:</P><P>ID&nbsp; &nbsp; &nbsp; &nbsp;VISITNUM&nbsp; &nbsp;maxofdat1dat2&nbsp; &nbsp; &nbsp; &nbsp; dat1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dat2<BR />304&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11JUN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07MAR2019&nbsp; &nbsp; &nbsp; 11JUN2019<BR />304&nbsp; &nbsp; &nbsp;101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13JUN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13JUN2019&nbsp; &nbsp; &nbsp; &nbsp;13JUN2019<BR />304&nbsp; &nbsp; &nbsp;102&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12JUL2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12JUL2019&nbsp; &nbsp; &nbsp; &nbsp;12JUL2019<BR />304&nbsp; &nbsp; &nbsp;103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 09AUG2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;09AUG2019&nbsp; &nbsp; &nbsp; 09AUG2019<BR />175&nbsp; &nbsp; &nbsp;107&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28JUN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28JUN2019&nbsp; &nbsp; &nbsp; 28JUN2019<BR />175&nbsp; &nbsp; &nbsp;106&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29MAY2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;29MAY2019&nbsp; &nbsp; &nbsp; 29MAY2019<BR />175&nbsp; &nbsp; &nbsp;105&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02MAY2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02MAY2019&nbsp; &nbsp; &nbsp; &nbsp;02MAY2019<BR />175&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10JAN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15OCT2018&nbsp; &nbsp; &nbsp; &nbsp;10JAN2019<BR />175&nbsp; &nbsp; &nbsp;102&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07MAR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07FEB2019&nbsp; &nbsp; &nbsp; &nbsp; 07MAR2019<BR />175&nbsp; &nbsp; &nbsp;103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 04APR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 08MAR2019&nbsp; &nbsp; &nbsp; &nbsp;04APR2019<BR />175&nbsp; &nbsp; &nbsp;104&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05APR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05APR2019&nbsp; &nbsp; &nbsp; &nbsp; 05APR2019<BR />175&nbsp; &nbsp; &nbsp;101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10JAN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10JAN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10JAN2019</P><P>164&nbsp; &nbsp; &nbsp;106&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23MAY2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23APR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23MAY2019<BR />164&nbsp; &nbsp; &nbsp;103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20JUN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25FEB2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20JUN2019<BR />164&nbsp; &nbsp; &nbsp;108&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 16JUL2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13JUL2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 16JUL2019<BR />164&nbsp; &nbsp; &nbsp;107&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20JUN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 17JUN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20JUN2019<BR />164&nbsp; &nbsp; &nbsp;104&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28MAR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25MAR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28MAR2019<BR />164&nbsp; &nbsp; &nbsp;102&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31JAN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28JAN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 31JAN2019<BR />164&nbsp; &nbsp; &nbsp;105&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25APR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20APR2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25APR2019<BR />164&nbsp; &nbsp; &nbsp;109&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13AUG2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13AUG2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13AUG2019<BR />164&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21DEC2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01JUN1995&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21DEC2018<BR />164&nbsp; &nbsp; &nbsp;101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03JAN2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31DEC2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03JAN2019</P><P>&nbsp;</P><P>&nbsp;</P><P>Data B:</P><P>&nbsp;</P><P>ID&nbsp; &nbsp; &nbsp; &nbsp;dat3&nbsp;<BR />304&nbsp; &nbsp; &nbsp;06MAY2019<BR />304&nbsp; &nbsp; &nbsp;13JUN2019<BR />304&nbsp; &nbsp; &nbsp;13JUL2019<BR />304&nbsp; &nbsp; &nbsp; 09AUG2019</P><P>175&nbsp; &nbsp; &nbsp; 04FEB2019<BR />175&nbsp; &nbsp; &nbsp; 08MAR2019<BR />175&nbsp; &nbsp; &nbsp; 07MAR2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp;02MAY2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp;28JAN2019<BR />175&nbsp; &nbsp; &nbsp; 28JUN2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp;07MAR2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; 08MAR2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; 10JAN2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04FEB2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07MAR2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; 10JAN2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28JAN2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07MAR2019<BR />175&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07MAR2019</P><P>164&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28FEB2019<BR />164&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28MAR2019<BR />164&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28MAR2019<BR />164&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20JUN2019<BR />164&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28MAR2019<BR />164&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04JAN2019</P><P>&nbsp;</P><P>Here is my derivation for visitnum:</P><P>"Assigned from data A, assign visitnum for each date in data B by comparing to dat1 and dat2 in data A. If there are more than 1 VISITNUM that includes the date, then assign VISITNUM as the larger VISITNUM number. If the date is not included in any VISITNUM, then choose the LAST (largest) VISITNUM where dat3 &gt; dat2."</P><P>&nbsp;</P><P>Can anyone provide me code for this logic.&nbsp;</P><P>&nbsp;</P><P>And please let me know if anything. Thank you in advance!</P><P>&nbsp;</P><P>Regards,</P><P>Adi</P><P>&nbsp;</P> Wed, 11 Sep 2019 15:10:07 GMT https://communities.sas.com/t5/New-SAS-User/Need-a-unique-visitnum-after-merging-multiple-records/m-p/587899#M14751 chinna0369 2019-09-11T15:10:07Z Re: Need a unique visitnum after merging multiple records https://communities.sas.com/t5/New-SAS-User/Need-a-unique-visitnum-after-merging-multiple-records/m-p/588104#M14765 <P>Posting data in usable form and showing the expected result ist always useful, if you expect a tested program as answer. You should also explain the role of "maxofdat1dat2".</P> Thu, 12 Sep 2019 05:28:36 GMT https://communities.sas.com/t5/New-SAS-User/Need-a-unique-visitnum-after-merging-multiple-records/m-p/588104#M14765 andreas_lds 2019-09-12T05:28:36Z