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: 
France
‎02-19-2019
  • 148 Posts
  • 37 Likes Given
  • 0 Solutions
  • 2 Likes Received

exclude all 'company_name' variable if one of the 'year' variable smal...

by Quartz | Level 8 in SAS Programming
‎02-17-2019 12:13 PM
‎02-17-2019 12:13 PM
Dear all,   how can I exclude all 'company_name' variable if one of the 'year' variable( belongs to the same 'company_name' group) smaller than 2009.     data old;<br />input <br />company_name $50. <br />year 50.<br />;<br />datalines; A, 1990<br />A, 2011<br />B, 2019 ; run;<code></code>      I expect to get the result like     B, 2019  as one 'year' variable of company_name 'A' variable is 1990 (which is smaller than 2009), so, I exclude all A company,     could you please give me some suggestions about this? thanks in advance   ... View more

Extracting date, month, year and time from a datetime variable

by Quartz | Level 8 in SAS Programming
‎02-17-2019 10:52 AM
‎02-17-2019 10:52 AM
Dear all,   how to extract the last four word from the variable, and recorded as a year? data date; input date $50.; datalines;JAN2009; run; could you please give me some suggestions? thanks in advance       ... View more

Re: how to match company name based on the first character of company ...

by Quartz | Level 8 in SAS Programming
‎02-08-2019 08:07 PM
‎02-08-2019 08:07 PM
Dear ballardw, thank you for your advice. what is the meaning of 'UNIGROUP' could you please explain it for me? ... View more

Re: ERROR: Sort execution failure.

by Quartz | Level 8 in SAS Programming
‎01-31-2019 01:46 PM
‎01-31-2019 01:46 PM
Daer Reeza, Could you please tell me how to change WHERE to ON conditions?     Besides, I simplify the code and run the following codes,   PROC SQL; CREATE TABLE Step4.M_3cit1 AS SELECT t_do.HRM_L2_cited, t_do.earliest_filing_month, t_do.earliest_publn_date, t_do.cited_docdb_family_id, t_do.docdb_family_id, t_ap2.appln_id, t_ap2.pat_publn_id, t_ap2.publn_date, t_ap2.HRM_L2_citing FROM step5.M_3cit_step1 AS t_do JOIN Step1.appln_publn AS t_ap2 ON t_do.docdb_family_id=t_ap2.docdb_family_id WHERE t_ap2.publn_date <= intnx('year', t_do.earliest_publn_date,3) /*a 3-year moving window starting at the earliest publication date of a patent family */ AND t_ap2.publn_date >= intnx('year', t_do.earliest_publn_date,0) GROUP BY HRM_L2_cited,t_do.cited_docdb_family_id,t_do.Docdb_family_id Having sum(HRM_L2_cited = HRM_L2_citing)>0 /* exclude self_citation */ ; QUIT; and the get following results,   238 PROC SQL; 239 CREATE TABLE Step4.M_3cit1 AS 240 SELECT 241 t_do.HRM_L2_cited, 242 t_do.earliest_filing_month, 243 t_do.earliest_publn_date, 244 t_do.cited_docdb_family_id, 245 t_do.docdb_family_id, 246 t_ap2.appln_id, 247 t_ap2.pat_publn_id, 248 t_ap2.publn_date, 249 t_ap2.HRM_L2_citing 250 FROM 251 step5.M_3cit_step1 AS t_do 252 JOIN Step1.appln_publn AS t_ap2 ON t_do.docdb_family_id=t_ap2.docdb_family_id 253 WHERE t_ap2.publn_date <= intnx('year', t_do.earliest_publn_date,3) /*a 3-year moving 253! window starting at the earliest publication date of a patent family */ 254 AND t_ap2.publn_date >= intnx('year', t_do.earliest_publn_date,0) 255 GROUP BY HRM_L2_cited,t_do.cited_docdb_family_id,t_do.Docdb_family_id 256 Having sum(HRM_L2_cited = HRM_L2_citing)>0 /* exclude self_citation */ 257 ; NOTE: The query requires remerging summary statistics back with the original data. NOTE: Invalid argument 2 to function INTNX. Missing values may be generated. NOTE: Invalid argument 2 to function INTNX. Missing values may be generated. ERROR: User asked for termination. 258 QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 18.27 seconds cpu time 0.78 seconds closing clipboard failed why there are 'NOTE: Invalid argument 2 to function INTNX. Missing values may be generated. NOTE: Invalid argument 2 to function INTNX. Missing values may be generated.'    Could you please give me some suggestion about this? thanks in advance.     ... View more

ERROR: Sort execution failure.

by Quartz | Level 8 in SAS Programming
‎01-29-2019 07:57 PM
‎01-29-2019 07:57 PM
Dear all,   When I run the code below,   PROC SQL; CREATE TABLE Step4.M_3cit AS SELECT t_base.HRM_L2_cited, t_base.earliest_filing_month, t_base.earliest_publn_date, t_do.cited_docdb_family_id, t_do.docdb_family_id, t_ap2.appln_id, t_ap2.pat_publn_id, t_ap2.publn_date, t_ap2.HRM_L2_citing FROM Pat_ori.Docdbfamiliescitations AS t_do JOIN Step3.M_2cit AS t_base ON t_do.cited_docdb_family_id=t_base.docdb_family_id_base JOIN Step1.appln_publn AS t_ap2 ON t_do.docdb_family_id=t_ap2.docdb_family_id WHERE t_ap2.publn_date <= intnx('year', t_base.earliest_publn_date,3) /*a 3-year moving window starting at the earliest publication date of a patent family */ AND t_ap2.publn_date >= intnx('year', t_base.earliest_publn_date,0) GROUP BY HRM_L2_cited,cited_docdb_family_id,t_do.Docdb_family_id Having sum(HRM_L2_cited = HRM_L2_citing)>0 /* exclude self_citation */ ORDER BY t_base.HRM_L2_cited ; QUIT; I get the  320 PROC SQL; 321 CREATE TABLE Step4.M_3cit AS 322 SELECT 323 t_base.HRM_L2_cited, 324 t_base.earliest_filing_month, 325 t_base.earliest_publn_date, 326 t_do.cited_docdb_family_id, 327 t_do.docdb_family_id, 328 t_ap2.appln_id, 329 t_ap2.pat_publn_id, 330 t_ap2.publn_date, 331 t_ap2.HRM_L2_citing 332 FROM 333 Con_ori.Docdbfamiliescitations AS t_do 334 JOIN Step3.M_2cit AS t_base ON t_do.cited_docdb_family_id=t_base.docdb_family_id_base 335 JOIN Step1.appln_publn AS t_ap2 ON t_do.docdb_family_id=t_ap2.docdb_family_id 336 WHERE t_ap2.publn_date <= intnx('year', t_base.earliest_publn_date,3) /*a 3-year moving 336! window starting at the earliest publication date of a patent family */ 337 AND t_ap2.publn_date >= intnx('year', t_base.earliest_publn_date,0) 338 GROUP BY HRM_L2_cited,cited_docdb_family_id,t_do.Docdb_family_id 339 Having sum(HRM_L2_cited = HRM_L2_citing)>0 /* exclude self_citation */ 340 ORDER BY t_base.HRM_L2_cited 341 ; NOTE: The query requires remerging summary statistics back with the original data. ERROR: Sort execution failure. 342 QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 2:10:07.98 cpu time 19:42.26 How can I overcome this problem? Could you please give me some suggestion about this ? thanks in advance ... View more

Re: import dataset

by Quartz | Level 8 in SAS Programming
‎01-24-2019 08:56 PM
‎01-24-2019 08:56 PM
dear Reeza,   Thanks for your kind advice.   But I do not understand the meaning of it. Could you please explain it for me by using the following example.   { "company_number":"09145694", "data": { "address": { "address_line_1":"St. Andrews Road", "country":"England" ,"locality":"Henley-On-Thames", "postal_code":"RG9 1HP", "premises":"2" }, "ceased_on":"2018-05-14", "country_of_residence":"England", "date_of_birth": { "month":2, "year":1977 }, "etag":"3b8caf795c03af63921e381f7bb8300a51ebb73d", "kind":"individual-person-with-significant-control", "links": { "self":"/company/09145694/persons-with-significant-control/individual/bIhuKnMFctSnjrDjUG8n3NgOrlU" }, "name":"Mrs Nga Thanh Wildman", "name_elements": { "forename":"Nga", "middle_name":"Thanh", "surname":"Wildman", "title":"Mrs" }, "nationality":"Vietnamese", "natures_of_control": ["ownership-of-shares-50-to-75-percent"], "notified_on":"2016-04-06" } }   this is a group of record in my dataset. could you please give me some suggestion about this?   ... View more

use the Marco to repeat code

by Quartz | Level 8 in SAS Programming
‎01-24-2019 10:03 AM
‎01-24-2019 10:03 AM
Dear all,   how to use the Marco to repeat code,   I am trying to repeat a process 10 times by the following code, %macro PreviousName; %do i=1 %to 10; data Sa_step2.House_company_base; set Sa_step2.House_company_base; _PreviousName_&i_CompanyName=upcase(_PreviousName_&i_CompanyName); call scan(_PreviousName_&i_CompanyName, -1, position, length); if scan(_PreviousName_&i_CompanyName,-1) in ('LTD' ,'LTD.','LIMITED.') then substr(_PreviousName_&i_CompanyName,position)='LIMITED'; drop position length; run; data sa_Step2.Patstat_gb_hrm; set Step2.Patstat_gb_hrm; rename PERSON_NAME=_PreviousName_&i_CompanyName; run; proc sort data=sa_Step2.Patstat_gb_hrm; by _PreviousName_&i_CompanyName; run; proc sort data=Sa_step2.House_company_base; by _PreviousName_&i_CompanyName; run; data STEP6.CompanyHouse_Patstat&i step4.Patstat_gb_hrm&i step3.CompanyHouse&i; merge Step2.Patstat_gb_hrm (in=ina) Sa_step2.House_company_base (in=inb) ; by _PreviousName_&i_CompanyName; if ina and inb then output STEP6.CompanyHouse_Patstat&i; else if ina then output step4.Patstat_gb_hrm&i; else if inb then output step3.CompanyHouse&i; run; %end; %mend PreviousName; %PreviousName run;   But it doesn't work.   926 %macro PreviousName; 927 %do i=1 %to 10; 928 929 data Sa_step2.House_company_base; 930 set Sa_step2.House_company_base; 931 _PreviousName_&i_CompanyName =upcase(_PreviousName_&i_CompanyName); 932 call scan(_PreviousName_&i_CompanyName, -1, position, length); 933 if scan(_PreviousName_&i_CompanyName,-1) in ('LTD' ,'LTD.','LIMITED.') then 933! substr(_PreviousName_&i_CompanyName,position)='LIMITED'; 934 drop position length; 935 run; 936 937 data sa_Step2.Patstat_gb_hrm; 938 set Step2.Patstat_gb_hrm; 939 rename PERSON_NAME=_PreviousName_&i_CompanyName; 940 run; 941 942 proc sort data=sa_Step2.Patstat_gb_hrm; 943 by _PreviousName_&i_CompanyName; 944 run; 945 proc sort data=Sa_step2.House_company_base; 946 by _PreviousName_&i_CompanyName; 947 run; 948 949 data STEP6.CompanyHouse_Patstat&i step4.Patstat_gb_hrm&i step3.CompanyHouse&i; 950 merge Step2.Patstat_gb_hrm (in=ina) 951 Sa_step2.House_company_base (in=inb) 952 ; 953 by _PreviousName_&i_CompanyName; 954 if ina and inb then output STEP6.CompanyHouse_Patstat&i; 955 else if ina then output step4.Patstat_gb_hrm&i; 956 else if inb then output step3.CompanyHouse&i; 957 run; 958 959 960 %end; 961 %mend PreviousName; 962 963 %PreviousName NOTE: Line generated by the invoked macro "PREVIOUSNAME". 1 data Sa_step2.House_company_base; set Sa_step2.House_company_base; _PreviousName_&i_CompanyName -------------- 180 1 ! =upcase(_PreviousName_&i_CompanyName); call scan(_PreviousName_&i_CompanyName, -1, position, 1 ! length); if scan(_PreviousName_&i_CompanyName,-1) in ('LTD' WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. NOTE: Line generated by the invoked macro "PREVIOUSNAME". 2 ,'LTD.','LIMITED.') then substr(_PreviousName_&i_CompanyName,position)='LIMITED'; drop position - 356 2 ! length; run; data sa_Step2.Patstat_gb_hrm; set Step2.Patstat_gb_hrm; rename WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. ERROR: Argument to SUBSTR must be character. ERROR 180-322: Statement is not valid or it is used out of proper order. NOTE 356-185: The SUBSTR pseudo-variable function does not allow character constants, expressions, or numeric constants for the first argument. NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 1:162 1:223 NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set SA_STEP2.HOUSE_COMPANY_BASE may be incomplete. When this step was stopped there were 0 observations and 57 variables. WARNING: Data set SA_STEP2.HOUSE_COMPANY_BASE was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.37 seconds cpu time 0.01 seconds NOTE: Line generated by the invoked macro "PREVIOUSNAME". 2 data sa_Step2.Patstat_gb_hrm; set Step2.Patstat_gb_hrm; rename 2 ! PERSON_NAME=_PreviousName_&i_CompanyName; run; proc sort - 22 200 WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. ERROR: Missing numeric suffix on a numbered variable list (_PreviousName_-i_CompanyName). ERROR: Old and new variable name specifications for RENAME must be of the same type. Statement is ignored. ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;. ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set SA_STEP2.PATSTAT_GB_HRM may be incomplete. When this step was stopped there were 0 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.07 seconds cpu time 0.01 seconds 22: LINE and COLUMN cannot be determined. NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, DECENDING, DESCENDING, DESENDING, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. 200: LINE and COLUMN cannot be determined. NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 200-322: The symbol is not recognized and will be ignored. WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. ERROR: Variable _PREVIOUSNAME_ not found. ERROR: Variable I_COMPANYNAME not found. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.04 seconds cpu time 0.01 seconds 22: LINE and COLUMN cannot be determined. NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, DECENDING, DESCENDING, DESENDING, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. 200: LINE and COLUMN cannot be determined. NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 200-322: The symbol is not recognized and will be ignored. WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. ERROR: Variable _PREVIOUSNAME_ not found. ERROR: Variable I_COMPANYNAME not found. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: Line generated by the invoked macro "PREVIOUSNAME". 5 Step2.Patstat_gb_hrm (in=ina) Sa_step2.House_company_base (in=inb) ; by 5 ! _PreviousName_&i_CompanyName; if ina and inb then output STEP6.CompanyHouse_Patstat&i; else - 22 200 WARNING: Apparent symbolic reference I_COMPANYNAME not resolved. ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;, DESCENDING, GROUPFORMAT, NOTSORTED, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set STEP6.COMPANYHOUSE_PATSTAT1 may be incomplete. When this step was stopped there were 0 observations and 61 variables. WARNING: The data set STEP4.PATSTAT_GB_HRM1 may be incomplete. When this step was stopped there were 0 observations and 61 variables. WARNING: The data set STEP3.COMPANYHOUSE1 may be incomplete. When this step was stopped there were 0 observations and 61 variables. NOTE: DATA statement used (Total process time): real time 0.20 seconds cpu time 0.01 seconds could you please give me some suggestions about it? thanks in advance   ... View more

Re: import dataset

by Quartz | Level 8 in SAS Programming
‎01-23-2019 08:05 PM
‎01-23-2019 08:05 PM
Dear Reeza, apology for my mistake. the dataset is downloaded from the below website. http://download.companieshouse.gov.uk/en_pscdata.html ... View more

Re: import dataset

by Quartz | Level 8 in SAS Programming
‎01-23-2019 03:33 PM
‎01-23-2019 03:33 PM
Dear Reeza, thanks for your message, sorry, I do not understand the meaning of XML and JSON. besides, the dataset is download by following the website (http://download.companieshouse.gov.uk/en_output.html) ... View more

import dataset

by Quartz | Level 8 in SAS Programming
‎01-23-2019 09:31 AM
‎01-23-2019 09:31 AM
dear all,    could you please give me some suggestion to import the below text file into sas. the sample of it has been added in the attachment.   three of the information is below,   {"company_number":"09145694","data":{"address":{"address_line_1":"St. Andrews Road","country":"England","locality":"Henley-On-Thames","postal_code":"RG9 1HP","premises":"2"},"ceased_on":"2018-05-14","country_of_residence":"England","date_of_birth":{"month":2,"year":1977},"etag":"3b8caf795c03af63921e381f7bb8300a51ebb73d","kind":"individual-person-with-significant-control","links":{"self":"/company/09145694/persons-with-significant-control/individual/bIhuKnMFctSnjrDjUG8n3NgOrlU"},"name":"Mrs Nga Thanh Wildman","name_elements":{"forename":"Nga","middle_name":"Thanh","surname":"Wildman","title":"Mrs"},"nationality":"Vietnamese","natures_of_control":["ownership-of-shares-50-to-75-percent"],"notified_on":"2016-04-06"}}   {"company_number":"08581893","data":{"address":{"address_line_1":"High Street","address_line_2":"Wendover","country":"England","locality":"Aylesbury","postal_code":"HP22 6EA","premises":"14a","region":"Buckinghamshire"},"ceased_on":"2016-07-01","country_of_residence":"England","date_of_birth":{"month":9,"year":1947},"etag":"45f9c9e5494b574eb52abc3990a49bd96fe09df3","kind":"individual-person-with-significant-control","links":{"self":"/company/08581893/persons-with-significant-control/individual/RgR9Zhc7yGhV0SBys8_WJ6H9O1o"},"name":"Mr Stephen Robert Charles Davies","name_elements":{"forename":"Stephen","middle_name":"Robert Charles","surname":"Davies","title":"Mr"},"nationality":"British","natures_of_control":["ownership-of-shares-25-to-50-percent","ownership-of-shares-25-to-50-percent-as-firm"],"notified_on":"2016-06-30"}}   {"company_number":"08581893","data":{"address":{"address_line_1":"High Street","address_line_2":"Wendover","country":"England","locality":"Aylesbury","postal_code":"HP22 6EA","premises":"14a","region":"Buckinghamshire"},"ceased_on":"2016-07-01","country_of_residence":"England","date_of_birth":{"month":6,"year":1965},"etag":"d55168c49f85ab1ef38a12ed76238d68f79f5a01","kind":"individual-person-with-significant-control","links":{"self":"/company/08581893/persons-with-significant-control/individual/-6HQmkhiomEBXJI2rgHccU67fpM"},"name":"Mr Quentin Colin Maxwell Solt","name_elements":{"forename":"Quentin","middle_name":"Colin Maxwell","surname":"Solt","title":"Mr"},"nationality":"British","natures_of_control":["ownership-of-shares-25-to-50-percent","voting-rights-25-to-50-percent"],"notified_on":"2016-06-30"}}   thanks in advance ... View more

Re: match two tables based on the processed company name

by Quartz | Level 8 in SAS Programming
‎01-19-2019 08:03 PM
‎01-19-2019 08:03 PM
dear mkeintz, thanks for your advice. could you please explain it more clearly. I am a beginner at SAS and not very understand. What is the meaning of 'unmatched_source', 'Source' ? could you please explain it by using the sample? thanks in advance. ... View more

Re: how to match company name based on the first character of company ...

by Quartz | Level 8 in SAS Programming
‎01-17-2019 10:28 PM
‎01-17-2019 10:28 PM
Dear Reeza,   thanks for your message. is it the advice?  This is a non-trivial problem because you end up doing N*M calculations which can be quite big.  Some tips: 1. Upcase everything 2. Change all & or other symbols such as @ from text 3. Ensure Co and/or LTD are standardized, i.e. LTD vs LTD. vs Limited vs Limited 4. Do an exact match and remove those records 5. Remove all spaces and try a second round of exact matches 6. Do fuzzy matching using COMPGED/COMPLEV/SOUNDS LIKE/SPEDIS and equality of first letter 7. Do fuzzy matching using COMPGED/COMPLEV/SOUNDS LIKE COMPGED works best in my experience https://communities.sas.com/t5/General-SAS-Programming/Matching-Company-Names/td-p/439674   however, I confuse about several steps. 1.'Change all & or other symbols such as @ from text'  how to change the symbols? should I remove all the symbols? or replace them with the same symbol?  2.'Ensure Co and/or LTD are standardized, i.e. LTD vs LTD. vs Limited vs Limited' how to standardize these words? Is there any code to do this step? particularly, will you remove the 'Co and/or LTD' words in a dataset, if few 'Co and/or LTD' words are recorded in the database? ... View more

Re: how to match company name based on the first character of company ...

by Quartz | Level 8 in SAS Programming
‎01-17-2019 09:23 AM
‎01-17-2019 09:23 AM
thanks Reeza, I will try it later(as my SAS is running the other program) besides, could you give me some suggestion if I really expect to match company name based on the first character of the company name? As my supervisor is asking me to match part of data handly. and I hope to improve the process by matching the first character through code ... View more

Re: Matching Company Names

by Quartz | Level 8 in SAS Programming
‎01-16-2019 11:46 PM
‎01-16-2019 11:46 PM
Hello cmoore, I am trying to match two datasets by company name as well. could you please share your code with me ? thanks in advacne best regards, France ... View more

how to match company name based on the first character of company name

by Quartz | Level 8 in SAS Programming
‎01-16-2019 10:45 PM
‎01-16-2019 10:45 PM
dear all,   how to merge two tables based on the first character of the 'company name' variables. bu using the 2 below tables as an example, table 1 Type NAME COMPANY NAME COMPANY NAME_1 COMPANY NAME - SHORT PREVIOUS NAME 917574 ABBOT GROUP DEAD - 07/03/08 ABBOT GROUP PLC ABBOT GROUP PLC. ABBOT GROUP PLC UNIGROUP 991144 ABBOTT LABS.GBP (LON) DEAD - DEAD ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABORATORIES ABOTT LABS.GBP (LON) 671863 ABBOTT LABS.USD (LON) DEAD - 21/10/16 ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABS. (XSQ) 926020 ABERCOM GROUP (LON) ABERCOM GROUP LTD. ABERCOM GROUP ABERCOM GROUP LTD   911147 ABERDEEN STEAK HOUSES DEAD - 03/01/03   ABDN.STEAK HSES.GP.PLC.     902737 ABERDEEN TRUST   ABERDEEN ASSET MAN.PLC.     507161 ABDN.TST.WTS.A EXPIRY 30/09/99   ABERDEEN ASSET MAN.PLC.     961337 ABERFORTH SMALLER COS.C   ABERFORTH SMCOS.IT.PLC.     928988 ABERFORTH SMCOS.C   ABERFORTH SMCOS.IT.PLC.     960130 ABERFORTH SPL.C SHARE DELISTED 27/07/94   ABERFORTH     15295C ABERTIS (IRS) ABERTIS INFRAESTRUCTURAS ABERTIS INFSTS.SA ABERTIS INFRAEST ACESA INTRAESTRUCTU(IRS) 29253E ABERTIS (IRS) BONUS RIGHTS 03/08/04 ABERTIS INFRAESTRUCTURAS ABERTIS INFSTS.SA ABERTIS INFRAEST ACESA INTRAESTRUCTU(IRS) 256273 ABGENIX (IRS) DEAD - 12/11/07 ABGENIX, INC. ABGENIX INCO. ABGENIX, INC.       table 2 code psn_name 1 ABBOTT CCCC 2 ABERDEEN ww 3 ACESA des 4 APPLE  5 ABERFORTH ftrr 6 ABDN dggg logic: 1splite each observation based on the 'blank' ',' '.' etc. in both tables. 2merge 'psn_name' in table 2 with each company name variable in table 1(i.e., NAME,  COMPANY_NAME, COMPANY_NAME_1,COMPANY_NAME_SHORT,PREVIOUS_NAME) if 1)both of the characters is the first character of the 'company name' variable, and 2) both of the characters are same. if the psn_name can be matched with any of the 'company name' variables, it can be recorded with the entire row. 3 record the matched table,  non_matched table 1 and non_matched table 2 separately.     so, for the  code psn_name 1 ABBOTT CCCC   the first character of psn_name variable is 'ABBOTT'.  so it can match with  991144 ABBOTT LABS.GBP (LON) DEAD - DEAD ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABORATORIES ABOTT LABS.GBP (LON) 671863 ABBOTT LABS.USD (LON) DEAD - 21/10/16 ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABS. (XSQ)   as the first character of the variable is 'ABBOTT' as well   the second example, 6 ABDN dggg so it can match with  911147 ABERDEEN STEAK HOUSES DEAD - 03/01/03   ABDN.STEAK HSES.GP.PLC.      as the 'ABDN' is as same as the 'ABDN' in 'ABDN.STEAK HSES.GP.PLC.'     Could you please give me some suggestions about this? the sample has been added to the attachment. thanks in advance.   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎02-19-2019 10:23 PM
Likes given to
Likes from
View All
Quick links