How to flag duplicates and calculate time between duplicates records?

I have COVID vaccination data:

MemberID	VaccinationDate	FullyVaccinated	Days Between Vaccinations
JohnDoe	1/6/2021	Y	28
JohnDoe	2/3/2021	Y	28
JaneSmith	1/20/2021	N	TBD

I'm provided with columns 1 and 2 and I'm trying to figure out how to create column 3 and 4 across a large dataset. I've found some articles on dealing with duplicates that say to use the summarize tool, but I can't seem to get that to work.

Workflow

Tips and Tricks

Accepted answers

Qiu

@SideOfRanch
Hope I understand your logic correctly.

0114-DataMafia.yxmd

All comments

SideOfRanch

I might prefer to see it as a single row per memberid, so final output might look like

MemberID	Vaccination1	Vaccination2	Vaccination3	FullyVaccinated	Days Between Vaccinations
JohnDoe	1/6/2021	2/3/2021		Y	28

I put three because I've already seen a record that has 2 vaccines on the same day.

Qiu

@SideOfRanch
Hope I understand your logic correctly.

0114-DataMafia.yxmd

SideOfRanch

@Qiu since you seem to have a firm grasp of how to do this, may I ask you one more question? You've answered my main question, but I'm trying to modify your method to include more elements.

Date Added to File	Name	Admin. Date	Product HCPCS/CPT Code	Product NDC	Vaccine Admin. Code	Admin. Brand	Submission Clarification Code
1/12/2021	JoeSchmoe	12/29/2020	91300		0001A	Pfizer
1/25/2021	JoeSchmoe	1/29/2021	91300		0002A	Pfizer
1/12/2021	JohnDoe	1/2/2021	91301		0011A	Moderna
1/25/2021	JohnDoe	1/2/2021	91300		0002A	Pfizer
1/12/2021	JohnDoe	2/3/2021	91301		0012A	Moderna
1/25/2021	JaneSmith	1/29/2021	91300		0002A	Pfizer

Lets say I want to roll this up and remove duplicates, which we didn't do in the last version.

So final output for Joe would be something like

	Vaccine Admin 1	Vaccine Admin Code 1	Vaccine Brand 1	Vaccine Admin 2	Vaccine Admin Code 2	Vaccine Brand 2
JoeSchmo	12/29/2020	0001A	Pfizer	1/29/2021	0002A	Pfizer

I think it's more of the same. I also didn't give an example output for the "error" of two vaccines on the same day, but I have to run. I really appreciate the help, learn something new each time I post.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703