@FreelanceReinh Thanks a lot. the solution you provided totally makes sense to me. More I think of and read on the subject 'confirmed' is highly likely means that both possibilities of True Positive and False Negative have been verified, hence, hospitalized. So, I'm strongly inclined to take 2,770 for a Total Disease (yes). I'll work on to calculate age, gender and province specific specificity tomorrow hoping that the multiple-testing per person scenario would not cause too much of a problem. Thanks again.  ... View more

Disease variable is implicit in the patient-level data that I have consisted of true disease cases (N=2,770). The variable negative is only available in the 'test' data and test data can be linked to the other datasets by the 'date' variable that they all share. the reason why province data has "confirmed" is that these separate and linkable datasets are inclusive of confirmed cases that can be found in the patient-level data, I realize. Yes, I can see now that I don't have to do three way or four way strata.  As you pointed out: if you know all these information... I'm staring at the data I have vs data I may not have required to calculate test specificity.  I calculated 1. Total N of test (N=395,194); 2. Total N of negative (N=372,002); 3. Total N of positive results (N=9,661, labelled as confirmed) compared to Total N of patients (true positive N=2,770) in the patient-level data. I just noted that the variable 'confirmed' in aggregated data sets are described as: Total N of positive results in the data dictionary. Which makes sense since it's few times higher than true positive cases N=2,770 which is the endpoints of the patient-level data. There is an inconsistency between these numbers. For example, the sum of 9,661 and 372,002 don't add up to 395,194 as shown in the screen-shot. Probably, because of missing data which I have no way of tracing down, I think.  Thanks for brainstorming with me and your questions. They helped.  Please let me know if any thoughts on what I shared here in the text and the screenshot below.    ... View more

@Kurt_Bremser @PeterClemmensen @Reeza @mkeintz @ChrisNZ @JerryV may interest you as well. I appreciate your help.  ... View more

@FreelanceReinh  Great questions! My goal is to calculate the test specificity based on the confirmed, negative and disease (yes, no). The patient-level data contains only confirmed cases. However, these separate aggregated data sets include the cases whose tests turned out negative. That's where the inconsistency of 5 prov (province) vs 91 provinces come from that you pointed out. What a catch there. Your questions made me realize that I don't have a patient-level 'proc freq' data for the 'negative' test cases which can be systematically different from the 'confirmed' cases. Is there thinkable estimate work that can be done if perfect precision was not sought after?    ... View more

GUESSINGROWS=MAX; Fixed the problem. Yes. The date variable broke had missing for about 98% of the data. Thank you very much Reeza. I greatly appreciate it. ... View more

quite handy! thanks a lot for the trick. ... View more

Thanks. format date best5.; This did the job. ... View more

I follow the logic. I appreciate how you took advantage of 'lag' function here. Amazing. ... View more

@mkeintz  Got it. I edited my original post a little bit. Would you please help me create NEW_ID variable shown in my edited post? I'm in a steep learning curve. If possible, only if possible, please let me know your approach to NEW_ID assuming that accounting for 'death' events in the process of assigning ID was the only wrinkle to worry about.  https://communities.sas.com/t5/SAS-Programming/Assign-Identifier-accounting-the-end-of-the-series-of-rows-by/m-p/633882#M188087 ... View more

@mkeintz  please ignore my previous comment. If I understood you approach correctly, right now, subscript IDs are marking where death is not accounted in the assignment of ID. I wonder how to assign ID through 'death' (DISP=20) and stops there to assign new sequential ID to the next record(s).    ... View more

Thank you so much. I used +1 instead +0.1 to keep my ID integer. Do you have any objection to that? ... View more