Ok, let's keep this super simple,   data have1; input var1 $ var2 & $8.; datalines; Dave Agenda 1 Kate Agenda 2 Mary Agenda 3 ;   data have2; input col1 & $ col2 dollar5.; datalines; Cost 1     $100 Cost 2     $200 Cost 3     $300 ; proc transpose data=have2 out=wide(drop=_NAME_) ;     id col1;     var col2; run;   Now please use the one to one merge to bring have1 and wide into PDV and write your IF THEN and SUM for each row based on your condition. I am too lazy do that piece. HTH!    data want; set have1; if _n_=1 then set wide; /*Your IF THEN will flow here*/ run;     ... View more

Yep, I agree with Reeza. It's a bit of a verbose to say the least. Can you please define the full business/tech problem to make it interesting? ... View more

Hi @RobertNYC Like Ballard pointed out, If you could provide some input sample and an output sample of your want will certainly make it convenient to discuss the best approach fit for your problem. Looking at your datastep, use of format with cntlin and cntlout seems to be a better approach than hash in my opinion.    I do not disagree with your choice of hash however I'd like to analyse different approaches, ease of coding and maintanence and so on before deciding what to implement.   @ballardw elegently pointed out emphasizing the application of formats when you have look up values in the form of ranges. I believe, Formats work so much more better with ranges than hashes. I'll await for more responses as I might be wrong but I severely doubt it though. Thanks!   ... View more

I'm afraid drop is a compile time statement and not an executable statement. Therefore you cannot conditionally execute a drop statement. You would have to choose your original idea of the macro where you can generate a drop statement based on  macro if condition. ... View more

btw you need an AND operator rather than OR:   %if (%scan(&dname.,-1,'_') ne scores.tsv and %scan(&dname.,-1,'_') ne income.tsv) %then %do; ... View more

Please paste the log. Run with options mlogic and mprint and paste the log here. Thanks ... View more

Try removing quotes like -   %macro xyz(dname=); data t; Length X $15; %if (%scan(&dname.,-1,'_') ne scores.tsv or %scan(&dname.,-1,'_') ne income.tsv) %then %do; x='Finance Data'; %end; %else %do; X='Scores,Income'; %end; run; %mend xyz; %xyz(dname=dataset_scores.tsv); %xyz(dname=dataset_income.tsv); %xyz(dname=dataset.tsv); ... View more

@ArseneWenger  My humble suggestion is to proceed with @ballardw simple yet neat approach with one pass and done and that is pretty much what all of us would. But here you go, just for your needs, have fun.- Nonetheless, I would never do this for my work   data try; input id status; datalines; 1 8 1 . 1 1 1 . 2 8 2 . 2 . 2 . 3 1 3 . 3 8 3 . 4 . 4 . 4 . 5 . 5 . 5 8 5 . ; run;     data want;     if _N_ = 1 then do;       declare hash h(dataset: 'try', ordered: 'yes',multidata:'yes');       declare hiter iter('h');       h.defineKey('id');       h.defineData('id','status');       h.defineDone();       call missing(id, status);    end;    rc = iter.first();    do while (rc = 0);    if lag(id) ne id then tag=0;    if status=8 then tag=1;    else if status=1 then tag=0;      output;       rc = iter.next();    end;    drop rc; run;   ... View more

Hi @ArseneWenger Sir/Madam, There's no reason for you get so heated about anything over a person or people just because they gave their opinion. Chill. The point all the super users were trying to make is that your question doesn't quite warrant a hash solution is all. All the three who responded in this thread are really amazing people and I have shamelessly used their solutions over the years.    This is an amazing friendly forum and many have been benefitted(myself included) and so we shouldn't create room for unnecessary feelings of haste. The positive spirit of this forum should go on forever. Well, I hope.   OK, May i ask why do you want a hash solution? what are you trying to load in hash table? What would be your look up keys and what data would you associate with those keys? How would you add those to the table? Through direct load or load during the pass of dataset? If you choose the latter, how would you counter efficiency as this is going to be an overhead?  Now, if for some reason you loaded your data, how do you think the object dot notation method will fetch your  data? Doesn't this make a one full pass of a dataset, plus a hash table plus a routine of adding and fetching data into the PDV? So inessence, there is more I/O, memory and others etc to further affect performance?   If you can help me explain the above with logical reasoning and some critical thinking, we can make this discussion an enjoyable experience. Have a nice evening ... View more