topic Re: complex transposing in SAS Programming https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570141#M160748 <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/212762">@Dhana18</a>: please post data in usable form. Mon, 01 Jul 2019 05:54:28 GMT andreas_lds 2019-07-01T05:54:28Z complex transposing https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/569891#M160649 <P>i have a data like this</P><TABLE><TBODY><TR><TD>VisitDate</TD><TD>PatientID</TD><TD>IncidentID</TD><TD>Specimenr source</TD><TD>testtype</TD><TD>result</TD><TD>Treatment_Date1</TD><TD>Treatment1</TD></TR><TR><TD>7/6/2017</TD><TD>272358</TD><TD>2773233</TD><TD>p</TD><TD>1</TD><TD>0</TD><TD>7/6/2017</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/6/2017</TD><TD>272358</TD><TD>2773233</TD><TD>u</TD><TD>2</TD><TD>1</TD><TD>7/6/2017</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/17/2017</TD><TD>272358</TD><TD>2773233</TD><TD>ur</TD><TD>1</TD><TD>0</TD><TD>7/6/2017</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/17/2017</TD><TD>272358</TD><TD>2773233</TD><TD>e</TD><TD>1</TD><TD>0</TD><TD>7/6/2017</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/17/2017</TD><TD>259981</TD><TD>2783394</TD><TD>ur</TD><TD>2</TD><TD>1</TD><TD>########</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/17/2017</TD><TD>259981</TD><TD>2783394</TD><TD>p</TD><TD>1</TD><TD>1</TD><TD>########</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/17/2017</TD><TD>259981</TD><TD>2783394</TD><TD>r</TD><TD>2</TD><TD>1</TD><TD>########</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR><TR><TD>7/17/2017</TD><TD>259981</TD><TD>2783394</TD><TD>v</TD><TD>1</TD><TD>0</TD><TD>########</TD><TD>Ceftriaxone 250 mg IM&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;and i want it to be like this</P><TABLE><TBODY><TR><TD>CollDate</TD><TD>PatientID</TD><TD>IncidentID</TD><TD>Specimenr source1</TD><TD>Specimenr source2</TD><TD>Specimenr source3</TD><TD>Specimenr source4</TD><TD>testtype1</TD><TD>testtype2</TD><TD>testtype3</TD><TD>testtype4</TD><TD>result1</TD><TD>result2</TD><TD>result3</TD><TD>result4</TD><TD>Treatment_Date1</TD><TD>Treatment1</TD></TR><TR><TD>7/6/2017</TD><TD>272358</TD><TD>2773233</TD><TD>p</TD><TD>u</TD><TD>ur</TD><TD>e</TD><TD>1</TD><TD>2</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>7/6/2017</TD><TD>Ceftriaxone 250 mg IM in a single dose AND same day treatment with Azithromycin 1g orally in a single dose (Gonorrhea)</TD></TR></TBODY></TABLE> Fri, 28 Jun 2019 16:28:48 GMT https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/569891#M160649 Dhana18 2019-06-28T16:28:48Z Re: complex transposing https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570042#M160715 <P>CODE NOT TESTED</P> <P>&nbsp;</P> <PRE>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[&amp;n] (Specimenrsource testtype result Treatment_Date1 Treatment1)=); run;</PRE> Sat, 29 Jun 2019 12:53:01 GMT https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570042#M160715 Ksharp 2019-06-29T12:53:01Z Re: complex transposing https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570044#M160716 Hi:<BR /> 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.<BR /><BR />Cynthia Sat, 29 Jun 2019 14:10:34 GMT https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570044#M160716 Cynthia_sas 2019-06-29T14:10:34Z Re: complex transposing https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570112#M160733 Yes, the visit date and the collect date are the same.<BR /> Sun, 30 Jun 2019 17:17:19 GMT https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570112#M160733 Dhana18 2019-06-30T17:17:19Z Re: complex transposing https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570127#M160739 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?<BR /><BR />Cynthia Sun, 30 Jun 2019 22:49:46 GMT https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570127#M160739 Cynthia_sas 2019-06-30T22:49:46Z Re: complex transposing https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570141#M160748 <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/212762">@Dhana18</a>: please post data in usable form. Mon, 01 Jul 2019 05:54:28 GMT https://communities.sas.com/t5/SAS-Programming/complex-transposing/m-p/570141#M160748 andreas_lds 2019-07-01T05:54:28Z