for your first question   The order of variable SCORE in Data Set RESULT has changed:   Order is not preserved in a sql join. So results are pretty as much expected. ... View more

if you can type all the variables below query(add in more variab;les) should work   proc sql; create table want as select participantid , max(start005) as start005,max(start006) as start006, max(stop005) as stoop05,max(stop009)as stop009 from data group by participantid; quit; ... View more

something like this data dataout; set datain; if prxmatch("m/^John.*If_True.*mary$/i",trim(name)) > 0 then found=1; else found = 0; run; quit; if john is no exactly at begining then use .* instead of ^ and if mary is not exactly at the end then replace $ with .* . Please remmeber order of values is important   ... View more

using regular expressions you can do this way data want; set have; dob = input(prxchange( 's/.*(\d{2}\/\d{2}\/\d{4}).*/$1/', -1, field), ddmmyy10.); format dob ddmmyy10.; run; ... View more

Got into the same issue four years back and did what @Tom suggested, which fixed the issue and reason is what @Patrick mentioned. ... View more

(?<!\d) --negative look behind (?!\d) --negative lookahead This values are used to check whether a particular characters are present along with other  characters and hence they are called as zero value assertions.   ?< symbol indicates look behind, which is to check wether a particular value is there  in front of the value you are looking for ! symbol represents not equal to AKA negative Let us use easier example. if you values goathair, cowhair, cowsomething and you want to find value hair and also do not want to have goat infront of hair then you something like this (?<!goat)hair   (?<!\d) indicates that you donot want a digit(\d) in the front of your expression given     ?! symbol indicates lookahead, which is to check wether a particular value is there  in back of the value you are looking for ! symbol represents not equal to AKA negative Let us take an easier example. if you want to find value of  hairyboy goodboy hairykid and you want to find variable with hair and also do not want to pick hairyboy but the value should have hairy infront then  you do something like this hairy(?!boy)     \d{4}(?!\d)  --- \d{4} means a digit repeated 4 times after value and there is no digit after that.   I tried my best to explain, please let me know if something is unclear, confusing or wrong ... View more

When ever queryoptimization is needed, what I would do is see how each query works, which part of query is running slow and try to find why it i running slow and how can I enhance query performance. With proper techniques, lot of queries can be optimized. Say building indexes can help, a hash join could be better or a join by format. There could be many more techniques ... View more

what kind of datasets you have. what are the operations you have do. how many columns you need for output dataset. Whether you have indexes on your tables etc. ... View more