Creating a new variable

[Dhana18]

I have a data set that has

ID

CollDate1

Specimen1

TestType1

Result1

CollDate2

Specimen2

TestType2

Result2

CollDate3

Specimen3

TestType3

Result3

Treatment_Date1

STDCDCRecTreatment1

1

7/31/2019

Pharyngeal

Culture

Not Applicable (e.g. Culture positive for N. Meningitides)

2

7/31/2019

Urethral

Culture

GC Negative

3

7/30/2019

Urethral

Culture

GC Negative

4

7/30/2019

Endocervical

Culture

GC Negative

7/30/2019

Pharyngeal

Culture

Not Applicable (e.g. Culture positive for N. Meningitides)

5

7/30/2019

Endocervical

Culture

GC Negative

6

7/30/2019

Urethral

Culture

GC Negative

7

7/30/2019

Urethral

Culture

GC Negative

8

7/30/2019

Urethral

Culture

GC Negative

9

7/30/2019

Pharyngeal

Culture

GC Negative

7/30/2019

Urethral

Culture

GC Negative

10

7/30/2019

Urethral

Culture

GC Negative

11

7/30/2019

Endocervical

Culture

GC Negative

12

7/30/2019

Pharyngeal

GC nucleic acid amplification test (NAAT)

GC Positive

7/30/2019

Urine

GC nucleic acid amplification test (NAAT)

GC Positive

7/30/2019

Urethral

Culture

GC Positive

7/30/2019

Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)

13

7/30/2019

Urine

GC nucleic acid amplification test (NAAT)

GC Positive

8/1/2019

Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)

14

7/30/2019

Urine

GC nucleic acid amplification test (NAAT)

GC Positive

7/30/2019

Pharyngeal

GC nucleic acid amplification test (NAAT)

GC Positive

7/30/2019

Urethral

Culture

GC Positive

7/30/2019

Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)

15

7/30/2019

Urine

GC nucleic acid amplification test (NAAT)

GC Positive

7/30/2019

Urethral

Culture

GC Positive

7/30/2019

Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)

16

7/29/2019

Urethral

Culture

GC Positive

7/29/2019

Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)

17

7/29/2019

Urethral

Culture

GC Positive

7/29/2019

Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)

18

7/29/2019

Rectal

Culture

GC Negative

7/29/2019

Pharyngeal

GC nucleic acid amplification test (NAAT)

GC Negative

7/29/2019

Urethral

Culture

GC Negative

19

7/29/2019

Pharyngeal

Culture

Not Applicable (e.g. Culture positive for N. Meningitides)

20

7/29/2019

Endocervical

Culture

GC Negative

21

7/29/2019

Urethral

Culture

Not Applicable (e.g. Culture positive for N. Meningitides)

22

7/29/2019

Endocervical

Culture

GC Negative

I would like to know number of tests conducted for each ID and number of Spe collected and number of pos result for each ID. How do i create a separate column of number of spe number of tests and number of pos result by specimen.

Also i would like to get time frame between positive test and treatment date.

Please help

[Shmuel]

Try next code and adapt it to your request:

data temp;
 set have;
       keep ID Colldate1 Sprcimen1 TestTye1 Result1;
       output;  /* left test */
       if not missing (colldate2) then do;
           colldtae1 = colldate2; 
           specimen1 = specimen2;
           testtype1 = testtype2;
          result1 = result2;
          output;
       end;
run;

Now it is easy to count using proc freq or any other method.: