DATA Step, Macro, Functions and more

a match problem

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

a match problem

Hi,

 

   as i am confronted with a problem,

my data set like:

Capture.PNG

i have made a mistake in my code, the variable b3 is substred from the character variable b2(just using some conditions clauses,so it has some missing values).

now i find it did not match the variable b2.i want to get the right b2 for b3,other values for b2,we can delete.

 

i have tried to use the find functions ,like flag1 = find(b2,trim(b3)),but it did not worked.. i think it may be the missing value in b3.

i have no idea about how to do it.

 

Thank you!

 


Accepted Solutions
Solution
‎02-15-2017 03:34 AM
Super User
Super User
Posts: 7,970

Re: a match problem

The you need to split b3 off from the data and merge it back on again - I am not typing that test data out (which is why I asked for a datastet version of your test data), so this is just guess:

proc sql;
  create table WANT as
  select A.*,
           B.B3
  from   HAVE A
  left join (select CODE,B3 from HAVE where B3 is not null) B
  on      A.ID=B.ID
/* Here is the key point */
  and    index(A.B2,B.B3) > 0;
quit;

Do note, if there are multiple string with B3 for the same id then all will be merged.

View solution in original post


All Replies
Super User
Super User
Posts: 7,970

Re: a match problem

I have no clue as to what your question is?  To post a question which will result in an accurate answer follow the guidance at the bottom of the post - post test data in the form of a datastep (we are not here to do data entry for you), post example output as to what it should look like - and explain the logic between the two.  Also post any logs or what it is that "did not work".

From what I can see here:  find(b2,trim(b3))

Will not do anything as the string b3 is never in b2 for any rows.

Contributor
Posts: 48

Re: a match problem

i am sorry for my illustration of problems,

data set like:

Capture.PNG

and i want to get the result like:

Capture1.PNG

variable b3 is the characters which substred from variable b2.

i just want to get the matching b2 for b3, not like the picture1, it has mismatched.

 

Thank you!

Solution
‎02-15-2017 03:34 AM
Super User
Super User
Posts: 7,970

Re: a match problem

The you need to split b3 off from the data and merge it back on again - I am not typing that test data out (which is why I asked for a datastet version of your test data), so this is just guess:

proc sql;
  create table WANT as
  select A.*,
           B.B3
  from   HAVE A
  left join (select CODE,B3 from HAVE where B3 is not null) B
  on      A.ID=B.ID
/* Here is the key point */
  and    index(A.B2,B.B3) > 0;
quit;

Do note, if there are multiple string with B3 for the same id then all will be merged.

Contributor
Posts: 48

Re: a match problem



i am really appreciated for yout response.  sorry for disturbing you again. i have put my test(excel). as the dataset is large. so i make a subset sample of it.

 

inside the sheet.var-pro13 is like'b3'

var-headline is like 'b2'

 

and i am wondering the usage of  "have"  in your code.

 

Thank you!!

Super User
Super User
Posts: 7,970

Re: a match problem

Sorry, not downloading files.  Post test data in the form of a datastep as text in the post.  Follow this post if you need help - only need a few lines which illustrate the problem:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

In my code - and you will see this a lot in posts here - I call your input dataset HAVE, i.e. you could refer to it as WORK.HAVE, the dataset called HAVE in library WORK.  Much like I call the output dataset WANT.

 

Contributor
Posts: 48

Re: a match problem

thanks for you guidance.

data b11;
input gvkey $6. +1 companyname $ 20. +1  headline1 $61.  product $100.;
datalines;
001021 ImageWorks afp imaging announces the new ``digi-vet equine dr'	
001021 ImageWorks afp imaging corp. receives canadian government license 	      ``digi-vet equine dr'
001021 ImageWorks afp imaging introduces new digital imaging system	
001021 ImageWorks afp introduces general purpose, radiographic systems	
001021 ImageWorks imageworks adds variable fov feature to newtom vgi	            radiographic systems
001021 ImageWorks imageworks announces fda approval of the newtom 5g cone 	
001034 Zoetis     alpharma announces positive results from pivotal phase iii 
001034 Zoetis     alpharma announces the resubmission of a new drug application 	 new drug
001034 Zoetis     alpharma cites positive results from late-stage study 
001034 Zoetis     alpharma inc. announces availability of flector(r) patch	
001034 Zoetis     alpharma inc. announces positive results from a phase ii           flector(r) patch
;

may be the code is not right to get a dataset.

 

 

hope it help to  illustrate my problems. the headline is ''b2'' ,product is ''b3'' and for a better result.i need to group by the "gvkey''

 

Thank you!!

Contributor
Posts: 48

Re: a match problem

really appreciated for your kindness. i have solved the problem. using the sql left join.

like.

proc sql;
  create table WANT as
  select c5.*,b3.pro13
  from  c5 left join (select gvkey,pro13 from b3 where pro13 is not null) b3
  on c5.headline1 like '%' || trim(b3.pro13) || '%' and c5.gvkey = b3.gvkey;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 256 views
  • 0 likes
  • 2 in conversation