Hi @SAS_Muggle , Assuming my understanding of your requirement is correct,  methinks your question presents a neat use case for Hash suminc method. Of course, Hash requires significant RAM, nevertheless should be quick.   data Have; input ID $ Type $; datalines; 1 A 1 A 1 B 1 B 1 A 1 A 1 A 1 C 1 C 2 A ; run; data want ; retain _iorc_ 1 ; if _n_=1 then do; dcl hash h (suminc:'_iorc_') ; h.definekey('id','type' ) ; h.definedone( ); end ; set have ; h.ref ( ) ; h.sum (sum : _count) ; if _count > 2 then flag = '*' ; drop _: ; run ; ... View more

I believe you don't need another "WHERE" after the AND boolean operator in -   /*corrected*/ where t1.client_code like ('HOIST%') and t1.client_code in ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219')); ... View more

@ballardw  Never lost your sense of humor. Rather, yours gets better with age 🙂 I hope you're doing well. I am not so active here but always enjoyed reading your posts 🙂 ... View more

Great explanation @Kurt_Bremser  Thank you. I am kind of interested in finding out the performance differences in tasks that can be done in both i.e. where stuff overlap. Any thoughts? ... View more

"I was about to abandon SAS altogether due to 1. Not getting that SAS job I interviewed for 2. Not many SAS jobs in Australia 3. Not getting any SAS work experience at my current job. 4. General tread that SAS is losing market share to R and Python."   It seems to be increasingly the trend. I just love SAS and I really hope SAS prevails.    Feedback for @ChrisHemedinger & team, You folks know where I work and the info I am getting is that SAS is rather losing out on its pricing strategy than anything can better that. IMHO, We the SAS family should sincerely revisit and take feedback and the pricing folks should focus on "volume" based growth than "premium". Take it or leave it, I don't want us the last set of folks who were part of the religion called SAS. Thank you for reading!   ... View more

@Nietzsche  Wow. Honestly haven't read the entire thread. But one thing is for sure, I can sense great perseverance, sincerity and discipline in you that I never had and never will. Oh well, my happy go lucky- mind, body and soul is not suited for great responsibilities.  I am gonna share this link with my colleagues. I am sure you are going to inspire a lot. Thank you for posting. All the best!  ... View more

Hi @magicdj  Switch to using Dictionary tables- proc sql; create table vars_new as select * from dictionary.columns where libname = 'SASHELP' and memname = 'CLASS' ; quit ; Library Name Member Name Member Type Column Name Column Type Column Length Column Position Column Number in Table Column Label Column Format Column Informat Column Index Type Order in Key Sequence Extended Type Not NULL? Precision Scale Transcoded? SASHELP CLASS DATA Name char 8 24 1         0 char no 0 . yes SASHELP CLASS DATA Sex char 1 32 2         0 char no 0 . yes SASHELP CLASS DATA Age num 8 0 3         0 num no 0 . yes SASHELP CLASS DATA Height num 8 8 4         0 num no 0 . yes SASHELP CLASS DATA Weight num 8 16 5         0 num no 0 . yes ... View more

Hello @ everyone, Thank you so much for the responses. Please bear with me as I want to read your responses thoroughly and then acknowledge. I just want to take a moment to thank you real quick. I haven't read yet, but will do by end of day (EST) and come back to you with subjective context.  ... View more

data abc; input alt_acc$ acc x; datalines; 2123 001 1 2234 001 0 2345 001 0 Y123 002 4 Y345 002 7 Y513 002 0 Z123 002 6 L123 003 0 L234 003 1 ; run; data want; set abc; by acc; retain _x; if first.acc then _x =.; if _x = . ; if x=0 then _x=x; drop _x; run; ... View more

Or better- data have; input ID : $10. Car Boat House; cards; Joe 30 . . Joe . . 30 Karen . . . Matt . . 30 Matt . 30 . Matt 30 . . john 30 . . Mike 30 . . Mike . . 30 Tony . . 30 Tony . 30 . Tony 30 . . ; proc sql; create table want as select n(car,boat, house) as answer, count(id) as count from (select id, max(car) as car,max( boat) as boat,max( house) as house from have group by id) group by answer; quit; ... View more

Not a very good design of your source data, but if you are certain that's your input- data have; input ID : $10. Car Boat House; cards; Joe 30 . . Joe . . 30 Karen . . . Matt . . 30 Matt . 30 . Matt 30 . . john 30 . . Mike 30 . . Mike . . 30 Tony . . 30 Tony . 30 . Tony 30 . . ; proc sql; create table want as select answer, count(id) as count from (select id, sum(n(car, boat, house)) as answer from have group by id) group by answer; quit;   ... View more