@Tom: I have no clue. It just occurred to me to do it this way, and it didn't occur to me to use a picture format ;). Thanks for the reminder.  ... View more

@PaigeMiller : Two points: When datetime/timestamp variables come from MS SQL server, SAS auto-assigns a DATETIMEw.d format to them. Hence, there's no need to rely on variable names to determine which ones need to be reformatted, as it's enough to find out which ones have formats starting with "DATETIME". Reassigning the formats the way you suggest will rewrite a potentially large data set. It's unnecessary since to reassign a format, it suffices to modify only the data set's descriptor via either proc DATASETS or proc SQL without touching the actual data. Kind regards Paul D. ... View more

@Sathish_jammy: The issue is two-pronged: Your values are datetimes but you want to format them as dates. The only canned format I know of that does that is E8601DN. However, it displays datetimes as YYYY-MM-DD (which to me is the only sane way to format dates); yet you want it as MM/DD/YYYY. If you okay with YYYY-MM-DD instead, then E8601DN. can be used directly in reformatting code. If not, you'll have to create a format formatting date times as MM/DD/YYYY via creating a function with proc FCMP and using it to compile a special format. Whatever decision is made above, use SQL to list the variables whose format begins wiht "DATETIME" and compose the corresponding ALTER statement. First, suppose you're okay with YYYY-MM-DD (HAVE is just a test data set with datetime variables formatted as DATETIME22.3 and some other unrelated variables): data have ; dt = "10NOV2019:00:00:00"dt ; date_a = dt + 123456789 ; b_date = date_a + 123456789 ; format dt date_a b_date datetime22.3 ; retain otherc "otherc" othern1 111 othern2 222 ; run ; proc sql noprint ; select catx (" ", name, "format=e8601dn10.") into :dtv separated by "," from dictionary.columns where libname = "WORK" and memname = "HAVE" and format eqt "DATETIME" order varnum ; alter table have modify &dtv ; quit ;  If you run this code and look at HAVE, you'll see that the datetime variables are displayed as YYYY-MM-DD.    Now suppose that you still want the datetimes to be displayed as MM/DD/YYYY. In this case, you need to create a special format and then use it in SQL instead of E8601DN. This is one way to do it: proc fcmp outlib = work.f.f ; function dtfmt (dt) $ ; return (put (datepart (dt), mmddyy10.)) ; endsub ; quit ; option cmplib = work.f ; proc format ; value dtfmt (default=10) other=[dtfmt()] ; run ; proc sql noprint ; select catx (" ", name, "format=dtfmt.") into :dtv separated by "," from dictionary.columns where libname = "WORK" and memname = "HAVE" and format eqt "DATETIME" order varnum ; alter table have modify &dtv ; quit ; Now if you look at HAVE, you'll see that the datetime variables appear formatted as MM/DD/YYYY.   EDIT: As @Tom has pointed out, you don't have to create the DTFMT. format using proc FCMP; you can create a picture format instead, for example: proc format ; picture dtfmt (default=10) low-high = '%0m/01/%Y' (datatype=datetime) ; run ; Kind regards Paul D.            ... View more

@aljones1816; For each record in ONE, loop through the list of the comma-delimited IDs. If any ID is in TWO, break the loop, and the ID on which the loop has stopped is your guy. Otherwise the ID value stopping the loop will be missing, which is what you need.  In other (SAS) words: data one ; input id_field $35. ; cards ; a23g22,hh998 bg884 not,in,two g9932 gh994,f99g,4jgkf,fgldf ; run ; data two ; input id $ ; cards ; bg884 g9932 gh994 f99g 4jgkf ; run ; data want ; if _n_ = 1 then do ; dcl hash h (dataset:"two") ; h.definekey ("id") ; h.definedone () ; end ; set one two (obs=0) ; do _n_ = 1 by 1 until (h.check() = 0 or cmiss (id)) ; id = scan (id_field, _n_) ; end ; run ; By the nature of the algorithm, the number of output rows will equal that in the input.   Kind regards, Paul D. ... View more

@Bromlem: Thanks. I wonder what else doesn't work there ;).  ... View more

@Bromlem: I am not sure whether using CUROBS= option instead of _N_ will help in your situation. But I suspect that it quite possibly might because while _N_ has nothing to do with the input data set per se (it's just populated at the top of the implied DATA step loop with the number of its current iteration from an internal counter), the variable assigned to CUROBS= is linked with the physical row numbers of the data set specified in the SET statement. In fact, it reflects them accurately even in the case there's a WHERE clause or observations marked for deletion. So, what the heck, give it a shot (the variable Q will be auto-dropped):     data mylasr.out ; set mylasr.in curobs = q ; rownum = q ; run ;   For the sake of curiosity, you can also try the MONOTONIC() function, though I'd be very surprised if it worked:     data mylasr.out ; set mylasr.in ; rownum = monotonic() ; run ; It will be interesting to see what you will have discovered.   Kind regards Paul D.   ... View more

@sassy_lm: Read the file to find out what it the largest [id,bid] group on file Use the result to size up arrays and create the requisite number of array variables for the output Read the file again by [id,bid] (it's assumed sorted accordingly) and populate the arrays using the record number in each BY group as an index, starting for each BY group at 1 In other (i.e. SAS) words: data have ; input ID BID Dx_year dx_code $ short_description & :$12. ; cards ; 1 250 2000 12345 HTN 1 250 2000 12344 obesity 1 250 2000 12333 vomiting 2 870 2002 12223 TB 2 870 2002 14322 anemia 2 870 2003 12355 tobacco use 2 870 2003 14325 infection ; run ; proc sql noprint ; select cats (max (g)) into :g from (select count (*) as g from have group id, bid) ; quit ; %put &=g ; data want (drop = dx_year dx_code short_description) ; do _i_ = 1 by 1 until (last.bid) ; set have ; by id bid ; array dxy dx_year1-dx_year&g ; array dxc $ 8 dx_code1-dx_code&g ; array sd $12 short_description1-short_description&g ; dxy = dx_year ; dxc = dx_code ; sd = short_description ; end ; run ; Note: The implicit OUTPUT before RUN writes out the array variables populated through the iterations of the DoW-loop as one record. Then program control is passed back to the top of the step and the array variables are auto-populated with missing values since they are not retained.  You can kill the %PUT statement. It's just FYI. Kind regards Paul D.   ... View more

@Shmuel: This way, in the final step you still have to read HAVE in its entirety with all its variables, while the OP says this is what causes the job to either run forever or crash. ... View more

@lydiawawa: What you are asking to do is easy by assigning an appropriate expression to the KEY argument tag when the CHECK method is called, for example: data have ; input @1 date $23. x y :$1. z :$3. ; cards ; 2018-04-03 03:44:18.728 1 A A01 2018-04-03 07:40:02.221 2 B B02 2018-05-03 09:20:20.135 3 C C03 2018-06-03 14:50:11.752 4 D D04 2018-07-03 02:42:17.005 5 E E05 2018-08-05 01:22:20.264 6 F F06 2018-01-06 04:45:49.402 7 G G07 2018-11-06 04:09:50.710 8 H H08 2018-07-07 04:12:31.623 9 I I09 2018-12-11 04:11:01.528 10 J J10 ; run ; data dates ; input m_yy $4. ; cards ; 4-03 1-06 ; run ; data want (drop = m_yy) ; if _n_ = 1 then do ; if 0 then set dates ; dcl hash h (dataset: "dates") ; h.definekey ("m_yy") ; h.definedone () ; end ; set have ; if h.check (key: put (substr (date, 7), $4.)) = 0 ; run ;  However, from what you've said I suspect it's not the real snag. If you run into a problem just applying a WHERE clause to the input, hash code akin to the above isn't going to help you much. Most likely, in your input you have a "tail" of satellite variables, much more numerous than X Y Z I've included for the sake of a demo; and though WHERE moves only the records that qualify from the buffer to the PDV, it fails to relieve the I/O burden created by the satellites enough. With the hash code like above, the things are even worse because every record gets moved from the buffer into the PDV before the unwanted ones get discarded by the subsetting IF.   Hence, the strategy needs to be adjusted depending on the nature of your input data. Just to outline two extreme scenarios, suppose that the records you end up selecting constitute: but a fraction of the whole input or a lion's share of the input In both cases, you have very few records to either (1) filter in or (2) filter out. And in both cases, it makes sense to first identify those records by observation number with as little computer resource pain as possible and (1) employ some tactic to get those you need or (2) mark those you don't want as deleted. Either way, at this stage it makes sense to drop all the satellite variables, reading in only the key, and apply the subsetting criteria to it, so that in the end you end up with a list of record IDs (i.e. the observation numbers) you either (1) want or (2) don't want.   Let's first look at #1: data want (drop = m_yy) ; * hash to lookup m_yy ; dcl hash h (dataset: "dates") ; h.definekey ("m_yy") ; h.definedone () ; dcl hash r () ; * hash to store filtered-in RIDs ; r.definekey ("rid") ; r.definedone () ; dcl hiter ir ("r") ; * find needed RIDs ; do rid = 1 by 1 until (lr) ; * KEEP is critical in SET below ; set have (keep = date) end = lr ; if h.check (key: put (substr (date, 7), $4.)) = 0 then r.add() ; end ; * select only records with RIDs in hash R from HAVE ; do while (ir.next() = 0) ; set have point = rid ; output ; end ; stop ; set dates ; run ; In the extreme scenario #2, you don't want any record with M_YY in file DATES: data discard (keep = rid) ; if _n_ = 1 then do ; if 0 the set dates ; dcl hash h (dataset: "dates") ; h.definekey ("m_yy") ; h.definedone () ; end ; * KEEP is critical in SET below ; set have (keep = date) ; * find UNneeded RIDs ; if h.check (key: put (substr (date, 7), $4.)) = 0 ; rid = _n_ ; run ; data have ; set discard ; modify have point = rid ; remove ; run ; In this case, you just make a list of the unwanted RIDs in the first step (which reads nothing but the key) and in the second step, use that list to mark the respective records in the data set HAVE itself as "deleted". In this case, you (a) still have never read anything from HAVE except the key, (b) never written out a huge data set with all the satellites and only a few records discarded. You've merely marked the unwanted records as "deleted" in HAVE. So, in your program downstream you will just read the data set HAVE; and all the records marked for deletion will be automatically ignored.   Of course, there're other scenarios in between these two extremes. But you should be getting the drift. When you deal with voluminous data, it's not always clearly cut and one has to be inventive; it's an art as much as a science. At times, to engineer a successful ETL, one needs to do a distribution analysis on the keys only first and then write a dynamic program smart enough to choose a subsetting tactic based on the distribution.    Kind regards  Paul D. ... View more