Reshape Problem

[Budu Gulo]

I need your help in reshaping my data. My desired variables are name, code, date, and return. The numbers starting with 4 are Excel format dates.Thanks.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 Name str28 CA1956151098 str5 C str28 CA92863W1023 str4 E str45 ERROR str5 G str28 KR7071970008
"Code"  "PCH#( CA1956151098(RI), 1D )" "Code"  "PCH#( CA92863W1023(RI), 1D )" "Code" ""                                              "Code"  "PCH#( KR7071970008(RI), 1D )"
"42712" "29.03"                        "42712" "0"                            ""     "$$ER: E100,INVALID CODE OR EXPRESSION ENTERED" "42711" "-4.18"                       
"42713" "-12.5"                        "42713" "0"                            ""     ""                                              "42712" "3.57"                        
"42716" "0"                            "42716" "0"                            ""     ""                                              "42713" "-2.3"                        
"42717" "11.43"                        "42717" "0"                            ""     ""                                              "42716" "3.14"                        
"42718" "-5.13"                        "42718" "0"                            ""     ""                                              "42717" "-1.14"                       
"42719" "-10.81"                       "42719" "0"                            ""     ""                                              "42718" "-2.31"                       
"42720" "0"                            "42720" "0"                            ""     ""                                              "42719" "1.18"                        
"42723" "0"                            "42723" "0"                            ""     ""                                              "42720" "1.56"                        
"42724" "-3.03"                        "42724" "0"                            ""     ""                                              "42723" "2.3"                         
"42725" "12.5"                         "42725" "0"                            ""     ""                                              "42724" "-3.37"                       
"42726" "-19.44"                       "42726" "0"                            ""     ""                                              "42725" "-2.33"                       
"42727" "6.9"                          "42727" "0"                            ""     ""                                              "42726" "-2.78"                       
"42730" "0"                            "42730" "0"                            ""     ""                                              "42727" "1.22"                        
"42731" "0"                            "42731" "34.78"                        ""     ""                                              "42730" "-4.84"                       
"42732" "0"                            "42732" "20.97"                        ""     ""                                              "42731" ".85"                         
"42733" "0"                            "42733" "6.67"                         ""     ""                                              "42732" "0"                           
"42734" "-3.23"                        "42734" "0"                            ""     ""                                              "42733" "-1.68"                       
"42737" "0"                            "42737" "2.5"                          ""     ""                                              "42734" "0"                           
"42738" "10"                           "42738" "3.66"                         ""     ""                                              "42737" "0"                           
"42739" "0"                            "42739" "0"                            ""     ""                                              "42738" ".85"                         
"42740" "3.03"                         "42740" "12.94"                        ""     ""                                              "42739" "29.66"                       
"42741" "0"                            "42741" "-6.25"                        ""     ""                                              "42740" "3.27"                        
"42744" "0"                            "42744" "6.67"                         ""     ""                                              "42741" "5.06"                        
"42745" "0"                            "42745" "2.08"                         ""     ""                                              "42744" "29.82"                       
"42746" "0"                            "42746" "12.24"                        ""     ""                                              "42745" "17.63"                       
"42747" "5.88"                         "42747" "3.64"                         ""     ""                                              "42746" "29.98"                       
"42748" "-2.78"                        "42748" "3.51"                         ""     ""                                              "42747" "19.73"                       
"42751" "-2.86"                        "42751" "-6.78"                        ""     ""                                              "42748" "0"                           
"42752" "-2.94"                        "42752" "5.45"                         ""     ""                                              "42751" "-29.91"                      
"42753" "-3.03"                        "42753" "0"                            ""     ""                                              "42752" "0"                           
"42754" "6.25"                         "42754" "1.72"                         ""     ""                                              "42753" "0"                           
"42755" "17.65"                        "42755" "1.69"                         ""     ""                                              "42754" "0"                           
""      ""                             ""      ""                             ""     ""                                              "42755" "0"                           
end

[Budu Gulo]

Just to make my question more clearer, I am giving an example of my desired output.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date str12 name str28 code str45 return
20796 "CA1956151098" "PCH#( CA1956151098(RI), 1D )" "29.03"                                        
20797 "CA1956151099" "PCH#( CA1956151098(RI), 1D )" "-12.5"                                        
20800 "CA1956151100" "PCH#( CA1956151098(RI), 1D )" "0"                                            
20801 "CA1956151101" "PCH#( CA1956151098(RI), 1D )" "11.43"                                        
20802 "CA1956151102" "PCH#( CA1956151098(RI), 1D )" "-5.13"                                        
20803 "CA1956151103" "PCH#( CA1956151098(RI), 1D )" "-10.81"                                       
20804 "CA1956151104" "PCH#( CA1956151098(RI), 1D )" "0"                                            
20807 "CA1956151105" "PCH#( CA1956151098(RI), 1D )" "0"                                            
20808 "CA1956151106" "PCH#( CA1956151098(RI), 1D )" "-3.03"                                        
20796 "CA92863W1023" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20797 "CA92863W1024" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20800 "CA92863W1025" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20801 "CA92863W1026" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20802 "CA92863W1027" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20803 "CA92863W1028" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20804 "CA92863W1029" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20807 "CA92863W1030" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20808 "CA92863W1031" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20809 "CA92863W1032" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20810 "CA92863W1033" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20811 "CA92863W1034" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20814 "CA92863W1035" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20815 "CA92863W1036" "PCH#( CA92863W1023(RI), 1D )" "34.78"                                        
20816 "CA92863W1037" "PCH#( CA92863W1023(RI), 1D )" "20.97"                                        
20817 "CA92863W1038" "PCH#( CA92863W1023(RI), 1D )" "6.67"                                         
20818 "CA92863W1039" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20821 "CA92863W1040" "PCH#( CA92863W1023(RI), 1D )" "2.5"                                          
20822 "CA92863W1041" "PCH#( CA92863W1023(RI), 1D )" "3.66"                                         
20823 "CA92863W1042" "PCH#( CA92863W1023(RI), 1D )" "0"                                            
20824 "CA92863W1043" "PCH#( CA92863W1023(RI), 1D )" "12.94"                                        
20825 "CA92863W1044" "PCH#( CA92863W1023(RI), 1D )" "-6.25"                                        
    . "#ERROR"       ""                             "$$ER: E100,INVALID CODE OR EXPRESSION ENTERED"
20795 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-4.18"                                        
20796 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "3.57"                                         
20797 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-2.3"                                         
20800 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "3.14"                                         
20801 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-1.14"                                        
20802 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-2.31"                                        
20803 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "1.18"                                         
20804 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "1.56"                                         
20807 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "2.3"                                          
20808 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-3.37"                                        
20809 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-2.33"                                        
20810 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-2.78"                                        
20811 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "1.22"                                         
20814 "KR7071970008" "PCH#( KR7071970008(RI), 1D )" "-4.84"                                        
end
format %td date