Creating a new string variable from an existing string & exporting it to Excel

[Sunita Rao]

I have a string variable and it is in the following format: " Company Name (Exchange:Ticker)"

a. As shown below, most, but not all, observations, have a space in the beginning.
b. Each observation is of a different length.
c. Most importantly, some observations do not have an (Exchange:Ticker) at the end.

I want to create another string variable, called ExchangeTicker, consisting only of Exchange:Ticker, without the "(" & ")", if the observation has one. If an observation in the original string variable does not have an Exchange:Ticker at the end, it can be ignored.

So, for example, the first 3 entries for the new string variable ExchangeTicker will be as follows:


NasdaqGS:MSFT
NYSE:TWX
NasdaqGS:INAP
.
.
.
and so on.

1. What is the easiest way to do this?

2. After I have all the entries in ExchangeTicker how can export the new string variable to Excel?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str89 Name
"Microsoft Corporation (NasdaqGS:MSFT)"           
"Time Warner Inc. (NYSE:TWX)"                     
" BMC Software, Inc."                             
" BroadSoft, Inc."                                
" Entrust, Inc."                                  
" Borland Software Corporation"                   
" Internap Corporation (NasdaqGS:INAP)"           
" iVillage Inc."                                  
" J2 Interactive, LLC"                            
" Juniper Networks, Inc. (NYSE:JNPR)"             
" Marriott International, Inc. (NasdaqGS:MAR)"    
" JDA Software Group, Inc."                       
" Novell, Inc."                                   
" Quest Software Inc."                            
" RSA Security LLC"                               
" Software Aktiengesellschaft (XTRA:SOW)"         
" TiVo Solutions Inc."                            
" Verio Inc."                                     
" Digital River Inc."                             
" Panasonic Corporation of North America, Inc."   
" Everyone.net, Inc."                             
" NextiraOne, LLC"                                
" Ingenio, LLC"                                   
" SITEL Corporation"                              
" MegaPath Inc."                                  
" KYE Systems Corp. (TSEC:2365)"                  
" DISH Network Corporation (NasdaqGS:DISH)"       
" CGI Group Inc. (TSX:GIB.A)"                     
" salesforce.com, inc. (NYSE:CRM)"                
" ASK sa"                                         
" The Hearst Corporation"                         
" American Greetings Corporation"                 
" American Water Works Company, Inc. (NYSE:AWK)"  
" TigerLogic Corporation"                         
" CenturyLink, Inc. (NYSE:CTL)"                   
" Ebix, Inc. (NasdaqGS:EBIX)"                     
" United States Cellular Corporation (NYSE:USM)"  
" Pegasystems Inc. (NasdaqGS:PEGA)"               
" Edgewater Technology, Inc. (NasdaqGM:EDGW)"     
" StarTek, Inc. (NYSE:SRT)"                       
" 8x8, Inc. (NYSE:EGHT)"                          
" MicroStrategy Incorporated (NasdaqGS:MSTR)"     
" Infosys Limited (NSEI:INFY)"                    
" Magic Software Enterprises Ltd. (NasdaqGS:MGIC)"
" Interactive Intelligence Group, Inc."           
" Avaya Inc."                                     
" Moxie Software CIM Corp."                       
" Wipro Limited (BSE:507685)"                     
" Robert Bosch GmbH"                              
" Aastra Technologies Ltd."                       
" Time Inc."                                      
" DataMirror Corporation"                         
" SMS Management & Technology Limited"            
" HCL Technologies Limited (NSEI:HCLTECH)"        
" Intersystems U.S.A., Incorporated"              
" Eniro AB (publ) (OM:ENRO)"                      
" Sopra Steria Group (ENXTPA:SOP)"                
" Capcom Co., Ltd. (TSE:9697)"                    
" Konami Holdings Corporation (TSE:9766)"         
" UXC Limited"                                    
" Veolia Environnement S.A. (ENXTPA:VIE)"         
" Fischer International Systems Corporation"      
" ALR Technologies Inc. (OTCPK:ALRT)"             
" TomTom North America, Inc."                     
" Tavant Technologies Inc."                       
" AMSI"                                           
" LucasArts Entertainment Company, LLC"           
" GoldenGate Software, Inc."                      
" T-Systems International GmbH"                   
" RingCentral, Inc. (NYSE:RNG)"                   
" Cisco IronPort Systems LLC"                     
" Koch Media Holding GmbH"                        
" Larsen & Toubro Infotech Limited (NSEI:LTI)"    
" IRESS Limited (ASX:IRE)"                        
" Data#3 Limited (ASX:DTL)"                       
" EMC Publishing, LLC"                            
" LifeSize Communications Inc."                   
" BigBen Interactive (ENXTPA:BIG)"                
" Epic Systems Corporation"                       
" iWay Software, Inc."                            
" D-Link Systems, Inc."                           
" Sun Microsystems Laboratories, Inc."            
" Webroot Inc."                                   
" Metaswitch Networks Ltd."                       
" Itesoft S.A. (ENXTPA:ITE)"                      
" Progress SA"                                    
" Symantec Ati Corporation"                       
" Marchex, Inc. (NasdaqGS:MCHX)"                  
" ESET, LLC"                                      
" BlueCat Networks, Inc."                         
" Rhino Software, Inc."                           
" Connecta AB"                                    
" Zenprise, Inc."                                 
" Ab Initio Software Corporation"                 
" Adverline S.A."                                 
" Genius Electronic Optical Co., Ltd. (TSEC:3406)"
" BT Conferencing, Inc."                          
" Insider Pages, Inc."                            
" Promt Ltd."                                     
" Campus Management Corporation"                  
end

[William Lisowski]

Starting with your data in memory, the following code may point you in a useful direction.

generate p1 = strrpos(Name,"(")
generate p2 = strrpos(Name,")")
generate ExchangeTicker = substr(Name,p1+1,p2-(p1+1)) if p1>0 & p2>p1
drop p1 p2
drop if ExchangeTicker==""
list in 1/5
export excel ExchangeTicker using ET.xlsx, replace

. generate p1 = strrpos(Name,"(")

. generate p2 = strrpos(Name,")")

. generate ExchangeTicker = substr(Name,p1+1,p2-(p1+1)) if p1>0 & p2>p1
(63 missing values generated)

. drop p1 p2

. drop if ExchangeTicker==""
(63 observations deleted)

. list in 1/5

     +--------------------------------------------------------------+
     |                                         Name   ExchangeTic~r |
     |--------------------------------------------------------------|
  1. |        Microsoft Corporation (NasdaqGS:MSFT)   NasdaqGS:MSFT |
  2. |                  Time Warner Inc. (NYSE:TWX)        NYSE:TWX |
  3. |         Internap Corporation (NasdaqGS:INAP)   NasdaqGS:INAP |
  4. |           Juniper Networks, Inc. (NYSE:JNPR)       NYSE:JNPR |
  5. |  Marriott International, Inc. (NasdaqGS:MAR)    NasdaqGS:MAR |
     +--------------------------------------------------------------+

. export excel ExchangeTicker using ET.xlsx, replace
file ET.xlsx saved

[Sunita Rao]

Dear Prof. Lisowski,

Thank you very much for your message. I will try this out today.

Best,
Sunita

[Sunita Rao]

Dear Prof. Lisowski,

Thank you again. Your suggestions work beautifully.

It saved me tons of trouble!

Regards,
Sunita