Hi,
as i am confronted with a problem,
my data set like:
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!
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.
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.
i am sorry for my illustration of problems,
data set like:
and i want to get the result like:
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!
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.
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!!
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:
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.
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!!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.