topic Re: Transposing columns in SAS Programming https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620067#M182141 <P>This is exactly what PROC FREQ does.</P> Sun, 26 Jan 2020 15:50:06 GMT PaigeMiller 2020-01-26T15:50:06Z Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620065#M182139 <P>Deart experts,</P><P>&nbsp;</P><P>the data set I have:</P><TABLE><TBODY><TR><TD>Company_A</TD><TD>year</TD><TD>Deal.Type</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2017</TD><TD>Partnership</TD></TR><TR><TD>(R) DENTAL</TD><TD>2004</TD><TD>Acquisition</TD></TR><TR><TD>0811769 BC ULC</TD><TD>2010</TD><TD>Acquisition</TD></TR><TR><TD>0902337 BC</TD><TD>2011</TD><TD>Acquisition</TD></TR><TR><TD>1 MEDICAL DISTRIBUTION INC ASSETS</TD><TD>2011</TD><TD>Acquisition</TD></TR><TR><TD>10 P S</TD><TD>2007</TD><TD>Acquisition</TD></TR><TR><TD>1077801 BC</TD><TD>2016</TD><TD>Acquisition</TD></TR><TR><TD>10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA)</TD><TD>2011</TD><TD>Acquisition</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2010</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2010</TD><TD>PrivateEquity</TD></TR><TR><TD>10X FUND LP</TD><TD>2016</TD><TD>PrivateEquity</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>VentureFinancing</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>wanted data set is a brief over view, may would look like the pivot table in excel:</P><P>adding one colum that has total number of records grouped by company, year, deal.type</P><TABLE><TBODY><TR><TD>Company_A</TD><TD>year</TD><TD>Deal.Type</TD><TD>yearly_type_sum</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2016</TD><TD>Partnership</TD><TD>1</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2016</TD><TD>VentureFinancing</TD><TD>2</TD></TR><TR><TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD><TD>2017</TD><TD>Partnership</TD><TD>1</TD></TR><TR><TD>(R) DENTAL</TD><TD>2004</TD><TD>Acquisition</TD><TD>1</TD></TR><TR><TD>0811769 BC ULC</TD><TD>2010</TD><TD>Acquisition</TD><TD>1</TD></TR><TR><TD>0902337 BC</TD><TD>2011</TD><TD>Acquisition</TD><TD>1</TD></TR><TR><TD>1 MEDICAL DISTRIBUTION INC ASSETS</TD><TD>2011</TD><TD>Acquisition</TD><TD>1</TD></TR><TR><TD>1077801 BC</TD><TD>2016</TD><TD>Acquisition</TD><TD>1</TD></TR><TR><TD>10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA)</TD><TD>2011</TD><TD>Acquisition</TD><TD>1</TD></TR><TR><TD>10X FUND LP</TD><TD>2009</TD><TD>PrivateEquity</TD><TD>6</TD></TR><TR><TD>10X FUND LP</TD><TD>2010</TD><TD>PrivateEquity</TD><TD>2</TD></TR><TR><TD>10X FUND LP</TD><TD>2016</TD><TD>PrivateEquity</TD><TD>1</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>Partnership</TD><TD>1</TD></TR><TR><TD>10X GENOMICS</TD><TD>2015</TD><TD>VentureFinancing</TD><TD>3</TD></TR><TR><TD>10X GENOMICS</TD><TD>2016</TD><TD>Partnership</TD><TD>5</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>tried to achieve this using proc sql, but having difficulties.</P> Sun, 26 Jan 2020 15:40:12 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620065#M182139 jimmychoi 2020-01-26T15:40:12Z Re: Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620066#M182140 <P>There are some suggestions here that might help:</P> <P>&nbsp;</P> <P><A href="https://communities.sas.com/t5/SAS-Procedures/changing-columns-into-rows/td-p/27547" target="_blank">https://communities.sas.com/t5/SAS-Procedures/changing-columns-into-rows/td-p/27547</A></P> Sun, 26 Jan 2020 15:43:13 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620066#M182140 Norman21 2020-01-26T15:43:13Z Re: Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620067#M182141 <P>This is exactly what PROC FREQ does.</P> Sun, 26 Jan 2020 15:50:06 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620067#M182141 PaigeMiller 2020-01-26T15:50:06Z Re: Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620079#M182150 <PRE><CODE class=" language-sas">proc freq data=have noprint; tables company_a*year*deal_type/list out=want(drop=percent); run;</CODE></PRE> Sun, 26 Jan 2020 17:39:11 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620079#M182150 unison 2020-01-26T17:39:11Z Re: Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620086#M182154 <P>It looks like you're trying to count/attach counts.&nbsp;</P> <P>&nbsp;</P> <P>If you want counts overall, SQL is pretty good. If you want row numbers its not as good.&nbsp;</P> <P>&nbsp;</P> <P>If you want a 'counter' variable:</P> <P><A href="https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/" target="_blank">https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/</A></P> <P>&nbsp;</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">proc sql; create table want_SQL as select company_a, year, dealType, count(*) as num_records from have group by company_a, year, dealType; quit;</CODE></PRE> <P>PROC FREQ is more efficient though - it'll be faster and has other output options that can be helpful, such as percents of overall, row/column to get statistics more quickly.&nbsp;</P> <PRE><CODE class=" language-sas">proc freq data=have; table company_a*year*dealType / out = WANT_FREQ; run;</CODE></PRE> <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/215463">@jimmychoi</a>&nbsp;wrote:<BR /> <P>Deart experts,</P> <P>&nbsp;</P> <P>the data set I have:</P> <TABLE> <TBODY> <TR> <TD>Company_A</TD> <TD>year</TD> <TD>Deal.Type</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2017</TD> <TD>Partnership</TD> </TR> <TR> <TD>(R) DENTAL</TD> <TD>2004</TD> <TD>Acquisition</TD> </TR> <TR> <TD>0811769 BC ULC</TD> <TD>2010</TD> <TD>Acquisition</TD> </TR> <TR> <TD>0902337 BC</TD> <TD>2011</TD> <TD>Acquisition</TD> </TR> <TR> <TD>1 MEDICAL DISTRIBUTION INC ASSETS</TD> <TD>2011</TD> <TD>Acquisition</TD> </TR> <TR> <TD>10 P S</TD> <TD>2007</TD> <TD>Acquisition</TD> </TR> <TR> <TD>1077801 BC</TD> <TD>2016</TD> <TD>Acquisition</TD> </TR> <TR> <TD>10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA)</TD> <TD>2011</TD> <TD>Acquisition</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2010</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2010</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2016</TD> <TD>PrivateEquity</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>VentureFinancing</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>&nbsp;</P> <P>wanted data set is a brief over view, may would look like the pivot table in excel:</P> <P>adding one colum that has total number of records grouped by company, year, deal.type</P> <TABLE> <TBODY> <TR> <TD>Company_A</TD> <TD>year</TD> <TD>Deal.Type</TD> <TD>yearly_type_sum</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2016</TD> <TD>Partnership</TD> <TD>1</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2016</TD> <TD>VentureFinancing</TD> <TD>2</TD> </TR> <TR> <TD>(PERTHERA) PERSONALIZED CANCER THERAPY</TD> <TD>2017</TD> <TD>Partnership</TD> <TD>1</TD> </TR> <TR> <TD>(R) DENTAL</TD> <TD>2004</TD> <TD>Acquisition</TD> <TD>1</TD> </TR> <TR> <TD>0811769 BC ULC</TD> <TD>2010</TD> <TD>Acquisition</TD> <TD>1</TD> </TR> <TR> <TD>0902337 BC</TD> <TD>2011</TD> <TD>Acquisition</TD> <TD>1</TD> </TR> <TR> <TD>1 MEDICAL DISTRIBUTION INC ASSETS</TD> <TD>2011</TD> <TD>Acquisition</TD> <TD>1</TD> </TR> <TR> <TD>1077801 BC</TD> <TD>2016</TD> <TD>Acquisition</TD> <TD>1</TD> </TR> <TR> <TD>10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA)</TD> <TD>2011</TD> <TD>Acquisition</TD> <TD>1</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2009</TD> <TD>PrivateEquity</TD> <TD>6</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2010</TD> <TD>PrivateEquity</TD> <TD>2</TD> </TR> <TR> <TD>10X FUND LP</TD> <TD>2016</TD> <TD>PrivateEquity</TD> <TD>1</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>Partnership</TD> <TD>1</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2015</TD> <TD>VentureFinancing</TD> <TD>3</TD> </TR> <TR> <TD>10X GENOMICS</TD> <TD>2016</TD> <TD>Partnership</TD> <TD>5</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>tried to achieve this using proc sql, but having difficulties.</P> <HR /></BLOCKQUOTE> <P>&nbsp;</P> Sun, 26 Jan 2020 18:31:55 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620086#M182154 Reeza 2020-01-26T18:31:55Z Re: Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620103#M182164 <PRE><CODE class=" language-sas"> data have; input Company_A &amp; $100. year DealType :$100.; cards; (PERTHERA) PERSONALIZED CANCER THERAPY 2016 Partnership (PERTHERA) PERSONALIZED CANCER THERAPY 2016 VentureFinancing (PERTHERA) PERSONALIZED CANCER THERAPY 2016 VentureFinancing (PERTHERA) PERSONALIZED CANCER THERAPY 2017 Partnership (R) DENTAL 2004 Acquisition 0811769 BC ULC 2010 Acquisition 0902337 BC 2011 Acquisition 1 MEDICAL DISTRIBUTION INC ASSETS 2011 Acquisition 10 P S 2007 Acquisition 1077801 BC 2016 Acquisition 10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA) 2011 Acquisition 10X FUND LP 2009 PrivateEquity 10X FUND LP 2009 PrivateEquity 10X FUND LP 2009 PrivateEquity 10X FUND LP 2009 PrivateEquity 10X FUND LP 2009 PrivateEquity 10X FUND LP 2009 PrivateEquity 10X FUND LP 2010 PrivateEquity 10X FUND LP 2010 PrivateEquity 10X FUND LP 2016 PrivateEquity 10X GENOMICS 2015 Partnership 10X GENOMICS 2015 Partnership 10X GENOMICS 2015 VentureFinancing 10X GENOMICS 2015 VentureFinancing 10X GENOMICS 2015 VentureFinancing 10X GENOMICS 2015 VentureFinancing 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 Partnership 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing 10X GENOMICS 2016 VentureFinancing ; proc summary data=have nway; class Company_A year DealType; output out=want(drop=_type_ rename=(_freq_=yearly_type_sum)) ; run; </CODE></PRE> Mon, 27 Jan 2020 00:59:37 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620103#M182164 novinosrin 2020-01-27T00:59:37Z Re: Transposing columns https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620106#M182165 <PRE><CODE class=" language-sas"> data _null_ ; if _n_=1 then do; dcl hash H (ordered:'a') ; h.definekey ("Company_A", "year" ,"DealType") ; h.definedata ("Company_A", "year" ,"DealType","yearly_type_sum") ; h.definedone () ; end; set have end=z; if h.find() ne 0 then yearly_type_sum=1; else yearly_type_sum=sum(yearly_type_sum,1); h.replace(); if z; h.output(dataset:'want'); run;</CODE></PRE> Mon, 27 Jan 2020 01:24:18 GMT https://communities.sas.com/t5/SAS-Programming/Transposing-columns/m-p/620106#M182165 novinosrin 2020-01-27T01:24:18Z