SAS Support Communities

Here is the data set and what i need.  Thanks   date tcode tm TID Want TID GET_TID 3-Jan-15 22 663 1 1 1 4-Jan-15 00002J66 663 2 2 1 3-Jan-15 00011BS 663 2 3 1 4-Jan-15 00011DEVD 663 2 4 1 3-Jan-15 00011KANN 663 2 5 1 3-Jan-15 00011KRS 663 2 6 1 4-Jan-15 00011NLSH 663 2 7 1 4-Jan-15 00011PAD 663 2 8 1 3-Jan-15 00011USDE 663 2 9 1 3-Jan-15 00069Z001 663 2 10 1 4-Jan-15 10039 1049 2 11 1 3-Jan-15 10163 1049 2 11   3-Jan-15 10273 1049 2 11   3-Jan-15 10273 1049 6 11   3-Jan-15 10273 1049 7 11   3-Jan-15 10273 1049 8 11   3-Jan-15 10273 1049 9 11   4-Jan-15 10273 1049 10 11   4-Jan-15 10273 1049 11 11   4-Jan-15 10273 1049 12 11   4-Jan-15 10273 1049 13 11   4-Jan-15 10273 1049 14 11   4-Jan-15 10273 1049 15 11   3-Jan-15 10489 1049 2 12 1 3-Jan-15 10629 1049 2 13 1 ... View more

Sorry, for some reason the code does not work. ... View more

Please check the CAT column.  In the first data set the variables are missing.  In the second, the data set I want, they column CAT is populated.    Thanks so much ... View more

No difference.  I saw that the formatting was messed up, so I posted it again.  OI hope that the data and the question is clearer now. ... View more

Putting this question again because the formatting seems to be messed up   Dear All: My data set is as follows Date ST IDa IDb Cat JUN012018 500 A AA I JUN022018 500 A AA I JUN032018 500 A AA I JUN012018 501 A AA JUN022018 501 A AA JUN032018 501 A AA JUN012018 502 A AA I JUN012018 502 A AA I JUN012018 500 A AAB JUN022018 500 A AAB JUN032018 500 A AAB JUN012018 501 A AAB D JUN022018 501 A AAB D JUN032018 501 A AAB D JUN012018 502 A AAB JUN012018 502 A AAB     and so on The data set should look like this     Date ST IDa IDb Cat JUN012018 500 A AA I JUN022018 500 A AA I JUN032018 500 A AA I JUN012018 501 A AA I JUN022018 501 A AA I JUN032018 501 A AA I JUN012018 502 A AA I JUN012018 502 A AA I JUN012018 500 A AAB D JUN022018 500 A AAB D JUN032018 500 A AAB D JUN012018 501 A AAB D JUN022018 501 A AAB D JUN032018 501 A AAB D JUN012018 502 A AAB D JUN012018 502 A AAB D     Thanks Stan    ... View more

I did think about that.  But by what Variable? ... View more

If that order comes in at 9:29:59 it transacts against the order 20000199 that comes in at 9:30:00 so the transaction time is 9:30:00 for TNUM 10001 not 9:29:59.  Moreover, since 20000196 cannot fill the entire order 20000199 it is also filled by ORNUM 2000197   So the trade book should look like: ID        Type      BorS        ORNUM                 Time            BUYORNUM       SELLORNUM          TNUM      A           O           B              20000196             9:29:59 A            O           B              20000197             9:30:00 A            O           S              20000199              9:30:00 A           T                                                            9:30:00              20000196                 20000199         10001         A           T                                                              9:30:00             20000197                 20000199        10002    and not       A O B 20000196 9:29:59       A T B   9:29:59 20000196 20000199 10001     Once again my gratitude for all your help. ... View more

Hi ChrisNZ:    I get what your saying.    But what I posted is the sequential order of a trade book; and the sequencing is important.    For example if ORNUM 20000196 came in at 9:29:59 and is filled by BUYORNUM 20000199 the sequencing may be broken by your solution.   Thank you so much.  I hope you can tweak the code to get the sequencing.     Randy ... View more

Thanks so much.  But this does not work.  If you notice for ID = A at 9:30:00 SELLORNUM 20000199 is paired with two BUYORNUM  20000196 and 200197.  So this is not the desired output. @ChrisNZ wrote: Like this? data O; input (ID Type BorS ORNUM Time )(:$); cards; A O B 20000196 9:30:00 A O B 20000197 9:30:00 A O S 20000199 9:30:00 A O B 20000201 9:30:00 A O B 20000205 9:30:01 A O S 20000209 9:30:01 B O B 20000531 9:30:00 B O S 20000643 9:30:00 B O B 20000754 9:30:01 run; data T; input (ID Type BUYORNUM SELLORNUM TNUM Time )(:$); cards; A T 20000196 20000199 10001 9:30:00 A T 20000197 20000199 10002 9:30:00 A T 20000205 20000209 10004 9:30:01 B T 20000754 20000643 10010 9:30:01 run; data WANT; set O; if _N_=1 then do; dcl hash BUY(dataset:'T'); BUY.definekey('BUYORNUM'); BUY.definedata(all:'Y'); BUY.definedone(); dcl hash SELL(dataset:'T'); SELL.definekey('SELLORNUM'); SELL.definedata(all:'Y'); SELL.definedone(); if 0 then set T; end; call missing( BUYORNUM, SELLORNUM, TNUM ); output; RC=BUY.find(key:ORNUM); if RC=0 then do; output; RC=BUY.remove(); RC=SELL.remove(key:SELLORNUM); end; else do; RC=SELL.find(key:ORNUM); if RC=0 then do; output; RC=SELL.remove(); RC=BUY.remove(key:BUYORNUM); end; end; drop RC; run;   ID Type BorS ORNUM Time BUYORNUM SELLORNUM TNUM A O B 20000196 9:30:00       A T B 20000196 9:30:00 20000196 20000199 10001 A O B 20000197 9:30:00       A T B 20000197 9:30:00 20000197 20000199 10002 A O S 20000199 9:30:00       A O B 20000201 9:30:00       A O B 20000205 9:30:01       A T B 20000205 9:30:01 20000205 20000209 10004 A O S 20000209 9:30:01       B O B 20000531 9:30:00       B O S 20000643 9:30:00       B T S 20000643 9:30:01 20000754 20000643 10010 B O B 20000754 9:30:01       B T B 20000754 9:30:01 20000754 20000643 10010     ... View more

data group3 ; set names; format Gr3 1. gr3 = 1 ; run;   data group3 ; set group3; if _N_ gt 3 then delete; run;   data group5 ; set names; format Gr5 1. gr5 = 1 ; run;   data group5 ; set group5; if _N_ gt 5 then delete; run;   data want; merge names group3 group5; by name; run;     ... View more