cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jimmychoi
Obsidian | Level 7 jimmychoi
Obsidian | Level 7
Go to Solution

Transposing columns

Posted 01-26-2020 10:39 AM (1235 views)

Deart experts,

 

the data set I have:

Company_AyearDeal.Type
(PERTHERA) PERSONALIZED CANCER THERAPY2016Partnership
(PERTHERA) PERSONALIZED CANCER THERAPY2016VentureFinancing
(PERTHERA) PERSONALIZED CANCER THERAPY2016VentureFinancing
(PERTHERA) PERSONALIZED CANCER THERAPY2017Partnership
(R) DENTAL2004Acquisition
0811769 BC ULC2010Acquisition
0902337 BC2011Acquisition
1 MEDICAL DISTRIBUTION INC ASSETS2011Acquisition
10 P S2007Acquisition
1077801 BC2016Acquisition
10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA)2011Acquisition
10X FUND LP2009PrivateEquity
10X FUND LP2009PrivateEquity
10X FUND LP2009PrivateEquity
10X FUND LP2009PrivateEquity
10X FUND LP2009PrivateEquity
10X FUND LP2009PrivateEquity
10X FUND LP2010PrivateEquity
10X FUND LP2010PrivateEquity
10X FUND LP2016PrivateEquity
10X GENOMICS2015Partnership
10X GENOMICS2015Partnership
10X GENOMICS2015VentureFinancing
10X GENOMICS2015VentureFinancing
10X GENOMICS2015VentureFinancing
10X GENOMICS2015VentureFinancing
10X GENOMICS2016Partnership
10X GENOMICS2016Partnership
10X GENOMICS2016Partnership
10X GENOMICS2016Partnership
10X GENOMICS2016Partnership
10X GENOMICS2016Partnership
10X GENOMICS2016Partnership
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing
10X GENOMICS2016VentureFinancing

 

 

wanted data set is a brief over view, may would look like the pivot table in excel:

adding one colum that has total number of records grouped by company, year, deal.type

Company_AyearDeal.Typeyearly_type_sum
(PERTHERA) PERSONALIZED CANCER THERAPY2016Partnership1
(PERTHERA) PERSONALIZED CANCER THERAPY2016VentureFinancing2
(PERTHERA) PERSONALIZED CANCER THERAPY2017Partnership1
(R) DENTAL2004Acquisition1
0811769 BC ULC2010Acquisition1
0902337 BC2011Acquisition1
1 MEDICAL DISTRIBUTION INC ASSETS2011Acquisition1
1077801 BC2016Acquisition1
10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA)2011Acquisition1
10X FUND LP2009PrivateEquity6
10X FUND LP2010PrivateEquity2
10X FUND LP2016PrivateEquity1
10X GENOMICS2015Partnership1
10X GENOMICS2015VentureFinancing3
10X GENOMICS2016Partnership5

 

tried to achieve this using proc sql, but having difficulties.

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User Reeza
Super User

Re: Transposing columns

Posted 01-26-2020 01:31 PM (1164 views)  |   In reply to jimmychoi

It looks like you're trying to count/attach counts. 

 

If you want counts overall, SQL is pretty good. If you want row numbers its not as good. 

 

If you want a 'counter' variable:

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

 

 

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;

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. 

proc freq data=have;
table company_a*year*dealType / out = WANT_FREQ;
run;
@jimmychoi wrote:

Deart experts,

 

the data set I have:

Company_A year Deal.Type
(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

 

 

wanted data set is a brief over view, may would look like the pivot table in excel:

adding one colum that has total number of records grouped by company, year, deal.type

Company_A year Deal.Type yearly_type_sum
(PERTHERA) PERSONALIZED CANCER THERAPY 2016 Partnership 1
(PERTHERA) PERSONALIZED CANCER THERAPY 2016 VentureFinancing 2
(PERTHERA) PERSONALIZED CANCER THERAPY 2017 Partnership 1
(R) DENTAL 2004 Acquisition 1
0811769 BC ULC 2010 Acquisition 1
0902337 BC 2011 Acquisition 1
1 MEDICAL DISTRIBUTION INC ASSETS 2011 Acquisition 1
1077801 BC 2016 Acquisition 1
10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA) 2011 Acquisition 1
10X FUND LP 2009 PrivateEquity 6
10X FUND LP 2010 PrivateEquity 2
10X FUND LP 2016 PrivateEquity 1
10X GENOMICS 2015 Partnership 1
10X GENOMICS 2015 VentureFinancing 3
10X GENOMICS 2016 Partnership 5

 

tried to achieve this using proc sql, but having difficulties.

 

View solution in original post

0 Likes
6 REPLIES 6
Norman21
Lapis Lazuli | Level 10 Norman21
Lapis Lazuli | Level 10

Re: Transposing columns

Posted 01-26-2020 10:43 AM (1232 views)  |   In reply to jimmychoi

There are some suggestions here that might help:

 

https://communities.sas.com/t5/SAS-Procedures/changing-columns-into-rows/td-p/27547

Norman.
SAS 9.4 (TS1M6) X64_10PRO WIN 10.0.17763 Workstation

0 Likes
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Transposing columns

Posted 01-26-2020 10:50 AM (1211 views)  |   In reply to jimmychoi

This is exactly what PROC FREQ does.

--
Paige Miller
0 Likes
unison
Lapis Lazuli | Level 10 unison
Lapis Lazuli | Level 10

Re: Transposing columns

Posted 01-26-2020 12:39 PM (1172 views)  |   In reply to jimmychoi 
proc freq data=have noprint;
tables company_a*year*deal_type/list out=want(drop=percent);
run;
-unison
0 Likes
Reeza
Super User Reeza
Super User

Re: Transposing columns

Posted 01-26-2020 01:31 PM (1165 views)  |   In reply to jimmychoi

It looks like you're trying to count/attach counts. 

 

If you want counts overall, SQL is pretty good. If you want row numbers its not as good. 

 

If you want a 'counter' variable:

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

 

 

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;

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. 

proc freq data=have;
table company_a*year*dealType / out = WANT_FREQ;
run;
@jimmychoi wrote:

Deart experts,

 

the data set I have:

Company_A year Deal.Type
(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

 

 

wanted data set is a brief over view, may would look like the pivot table in excel:

adding one colum that has total number of records grouped by company, year, deal.type

Company_A year Deal.Type yearly_type_sum
(PERTHERA) PERSONALIZED CANCER THERAPY 2016 Partnership 1
(PERTHERA) PERSONALIZED CANCER THERAPY 2016 VentureFinancing 2
(PERTHERA) PERSONALIZED CANCER THERAPY 2017 Partnership 1
(R) DENTAL 2004 Acquisition 1
0811769 BC ULC 2010 Acquisition 1
0902337 BC 2011 Acquisition 1
1 MEDICAL DISTRIBUTION INC ASSETS 2011 Acquisition 1
1077801 BC 2016 Acquisition 1
10TH RAMADAN FOR PHARMACEUTICALS AND DIAGNOSING PRODUCTS (RAMIDA) 2011 Acquisition 1
10X FUND LP 2009 PrivateEquity 6
10X FUND LP 2010 PrivateEquity 2
10X FUND LP 2016 PrivateEquity 1
10X GENOMICS 2015 Partnership 1
10X GENOMICS 2015 VentureFinancing 3
10X GENOMICS 2016 Partnership 5

 

tried to achieve this using proc sql, but having difficulties.

 

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Transposing columns

Posted 01-26-2020 07:59 PM (1130 views)  |   In reply to jimmychoi 

data have;
input Company_A & $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;
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Transposing columns

Posted 01-26-2020 08:24 PM (1118 views)  |   In reply to novinosrin 

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;
0 Likes

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎01-26-2020 10:39 AM
  • 1236 views
  • 0 likes
  • 6 in conversation