complex transposing

[Dhana18]

i have a data like this

VisitDate	PatientID	IncidentID	Specimenr source	testtype	result	Treatment_Date1	Treatment1
7/6/2017	272358	2773233	p	1	0	7/6/2017	Ceftriaxone 250 mg IM
7/6/2017	272358	2773233	u	2	1	7/6/2017	Ceftriaxone 250 mg IM
7/17/2017	272358	2773233	ur	1	0	7/6/2017	Ceftriaxone 250 mg IM
7/17/2017	272358	2773233	e	1	0	7/6/2017	Ceftriaxone 250 mg IM
7/17/2017	259981	2783394	ur	2	1	########	Ceftriaxone 250 mg IM
7/17/2017	259981	2783394	p	1	1	########	Ceftriaxone 250 mg IM
7/17/2017	259981	2783394	r	2	1	########	Ceftriaxone 250 mg IM
7/17/2017	259981	2783394	v	1	0	########	Ceftriaxone 250 mg IM

 and i want it to be like this

CollDate

PatientID

IncidentID

Specimenr source1

Specimenr source2

Specimenr source3

Specimenr source4

testtype1

testtype2

testtype3

testtype4

result1

result2

result3

result4

Treatment_Date1

Treatment1

7/6/2017

272358

2773233

p

u

ur

e

1

2

1

1

0

1

0

0

7/6/2017

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

[Ksharp]

CODE NOT TESTED

proc sql;
select max(n) into :n 
from (
select count(*) as n
 from have
  group by VisitDate,	PatientID,	IncidentID
);
quit;

proc summary data=have;
by VisitDate	PatientID	IncidentID;
output out=want idgroup(out[&n] (Specimenrsource	testtype	result	Treatment_Date1	Treatment1)=);
run;

[Cynthia_sas]

Hi:
Are CollDate and VisitDate the same? I don't see CollDate in the data you posted. Also, the text you show for Treatment1 under desired results is not found in your original data that you posted.

Cynthia

[Dhana18]

Yes, the visit date and the collect date are the same.

[Cynthia_sas]

What do the ##### mean for Treatment_Date1? And why is the value for Treatment1 different in the first table than you show in the second table?

Cynthia

[andreas_lds]

@Dhana18: please post data in usable form.