I'm really sorry..i misunderstood your question...i thought u were selecting member ID@1 instead of ID & @1 ..my bad..Anyhow it seems you fixed the issue...for the getting members name within the macro variable. use PROS SQL filename xx'xo94.control.userview' disp=shr; filename yy'xo94.control.user' disp=old; data siva; input id $; cards; srgm2010 srgm2001 ; run; proc sql; select id into : memlist separated by ' ' from siva; quit; proc pdscopy indd=xx outdd=yy; select &memlist; run; ... View more

You don't need to quote the member name. Remove the quotes around the member name id@1 and resubmit the code. It should work proc pdscopy indd=xx outdd=yy; select id @1; run; ... View more

🙂 ah that one ....the use of IF _N_ = 1 then do; or W/O it doesn't makes any difference( thank god i remember this 🙂 ) coz for constant regular expression the compilation happens only once and the value to the left side variable would be same for next successive observations as was for 1st observation. So successive calls to PRXPARSE will not cause a recompile, but will return the same value to the left side variable for the regular expression that was already compiled. Again, as you said - "many ways to skin a cat" 🙂 ..w/ or w/o doesn't makes any difference in case of constant regular expression like the one above. Thanks Chris, for pointing that out 🙂 ... View more

Hey Chris, you're right...totally forgot how to lessen the \d ...as i said, don't get to write perl regex these days...totally forgot about this 😞 Thanks for the updated regex! ... View more

Hey Peter/jpmoreno, the code for this problem in regular expression is very very simple. 10 time \d would get things going. data d1; input id $40.; datalines; 01234567890ABC 01234ABC567890 01ABC23456789001 ; run; data dx; set d1; re=prxparse('/\d\d\d\d\d\d\d\d\d\d/'); if prxmatch(re,id) then output; run; proc print;run; Maybe you guys already got the solution but couldn't stop myself writting a perl regex..i rarely get to write regex these day....it's so tempting and cool.. 🙂 Thanks! ... View more

Hey Sid, use the dataset options on the imcoming datasets @ merge step or from clause in sql. Based on data that you have got, you can yourself find out which technique would be better/efficient data d1; input id name $10.; datalines; 10 sushil 20 sas ;;; run; data d2; input id lname $10.; datalines; 10 nayak 20 base ;;; run; data final; merge d1(in=a firstobs=1 obs=1) d2(in=b where=(id<20)); by id; if a and b; run; proc print;run; Things to learn and understand: WHERE= dataset option usage and working in SQL and datastep Hope this helps 🙂 Thanks! ... View more

Hey Janne, i just saw that part of example code got skipped due to usage of Increment variable i within square brackets , and made last part of code into italic. Here is the fixed one data dx; opprim_lak=.; sluten_lak=.; a_sluten_lak=.; z_opprim_lak=.; run; data new; set dx; array nums{*} opprim_lak--z_opprim_lak;/*first var -- last var*/ do i=1 to hbound(nums); if nums{i}=. then nums{i}=0; end; drop i; run; proc print;run; ... View more

Hi Janne, As the variables are in sequence in dataset, beginning with opprim_lak till sluten_lak and all these variables count to 120 variables, you could try making an array of these variables(opprim_lak--z_opprim_lak) and then using a do loop take them all. If these variables are not in sequence then what you could do is, make a macro list using proc sql (where var like '%_lak%') separated by ' ' ( let say macro variable is &dummy) and then array nums &dummy; If the dataset has these 120 variables as numeric data types and there is no other numeric type variables in the dataset(there can be character type vriables) then you could use array nums _numeric_; example code: data dx; opprim_lak=.; sluten_lak=.; a_sluten_lak=.; z_opprim_lak=.; run; data new; set dx; array nums opprim_lak--z_opprim_lak; do i=1 to hbound(nums); if nums=. then nums=0; end; drop i; run; proc print;run; ... View more

Hey Learner, The macro is very simple. You just need to understand how the word breaking be done to assign that word to some variable. Here is the code :: %macro splitter(string=); %let cnt=1; %let num1=%scan(&string,&cnt,%str( )); %do %while(&num1 ne %str( )); %global word&cnt; %let word&cnt=&num1; %let cnt=%eval(&cnt+1); %let num1=%scan(&string,&cnt,%str( )); %end; %mend splitter; %splitter(string=base macros sql advance) %put _global_; ... View more

Hi SASPhile, Tim@SAS gave the perfect regular expression. I came up with re = PRXPARSE ("/[0-9]+(\.[0-9]*)?MG(\/[0-9]*ML| KIT|FLEX)?/i"); I realized its similar to what Tim@SAS posted 😞 . Just to add more to the logic and regular expression. Whenever u are trying to create a regular expression try and break down the pattern. In your case of provided data 1) Integer part hence [0-9]+ <--- i come from perl background hence used to [0-9] instead of \d, don't mind. 2) Decimal part(may or may not come hence optional) hence (\.[0-9]*)? 3) MG is just after the integer or decimal part (always coming) hence MG 4) (An Integer may or may not come with ML) or ( KIT may come with leading space) or (FLEX would come) ....as any of the three cases can happen or may not happen at all (e.g 10MG) hence above 3 cases are optional hence regular expression for this part would be ----> (\/[0-9]*ML| KIT|FLEX)? Now going back to the original regular expression that you coded RE = PRXPARSE ("/ \d{1,5}\.?\d{0,4}\?mg/i") . For the datalines written by Tim@SAS, your regular expression would not work. The reasons are : 1) PRXPARSE ("/ \d ..if you look closely, there is a space regulare expression is looking for before the integer. Maybe your data has that space, not sure thats why im pointing it out. 2) d{0,4}\? would match in case of 0.4?MG and not 0.4MG hence it should have been like d{0,4}? Even after fixing the above 2 things you would only get Integer + decimal + MG being pattern matched (e.g. 10.4MG , 10MG & 0.4MG ) the KITS/2ML/ML etc would still not get matched. For that check out the regular expression posted by me or Tim@SAS Njoy understanding the patterns!!!!!!!!!! 🙂 ... View more

Cool post Daniel.... Honestly the way i learnt functions was by practice, execution & Ronald P. Cody book on SAS Functions by example..by constant usage i have been able to remember which functions give default 200 length or input variable length itself...wierd lengths have not yet happend with me :)...Thanks for the SAS-L post link..i never knew about the subgroups in function ( Library and Supervisor Functions 😞 which makes this length assignment happen the way they do) . Thanks a ton!!!!!!!!! for the info 🙂 ... View more

Thanks! Daniel and Scott. I think i have got it, the sas during compilation does find the length based on first assignment of the variable to get the variable length eventhough the value is hardcoded text(actually the hardcoded text made me into thinking that the text was being read to get the length and so it is execution step which gets the length). For ex. in the below code, the length of country is assigned to 5. data x; stop; country='India'; country='Sri Lanka'; run; proc contents;run; Daniel - For X3 and X4, it's not the complexity but the function default length allocation which is causing 200 to show up on vlength function execution. For repeat and cat/x/s/t the default length of the left side variable is always 200, if we don't provide a length statement in advance to the left side variable first assignment. x=repeat('a',20) would also give length of 200. Thanks Again!! ... View more

Hey sas_grad, Here is a different solution to the problem. data dy; infile datalines dlm=',' missover; input x y cnt; datalines; 20, 100, 0 20, 100, 0 20, 100, 1 20, 100, 0 20, 100, 1 20, 100, 1 20, 100, 1 20, 100, 0 21, 101, 1 21, 101, 1 21, 101, 1 21, 101, 0 21, 101, 1 21, 101, 1 21, 101, 1 21, 101, 0 22, 102, 1 22, 102, 1 22, 102, 1 22, 102, 1 22, 102, 1 22, 102, 1 22, 102, 1 22, 102, 0 ; run; data dy; set dy; by notsorted x y cnt; retain count 0; if first.cnt then count=_n_; run; proc sql; create table final as select a.x, a.y, a.cnt,b.count from dy a ,( select x, y, cnt, count , count(count) as xx from dy group by x,y,cnt,count having count(count) >= 3 ) b where a.count=b.count; quit; proc print;run; Thanks!!! ... View more