BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JNWong
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

JNWong
Calcite | Level 5

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!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

JNWong
Calcite | Level 5


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!!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

JNWong
Calcite | Level 5

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!!

JNWong
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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