Note that NWAY contradicts the specifications in the TYPES statement. Apparently SAS ignores NWAY in this situation. ... View more

data want ; set have ; by Date_of_Purchase ; Count + first.Date_of_Purchase ; run ; ... View more

This is also a good place to utilize the use of DISTINCT with summary functions:    having count(distinct rec_id) GT 1 art297 wrote: Or, since you mentioned "final data set", if you want all of the records for cases that meet the criteria you could use something like: proc sql noprint;   create table want as     select *       from have         group by provider           having min(rec_id) ne max(rec_id)   ; quit; ... View more

A couple of other techniques: 1. NATURAL joins 2. inline views to add flag columns to each side of the join PS: Thanks for the plug! MikeZdeb wrote: Hi ... first, in your data step, I make some changes ... data merged_data; merge address_A (in = A) address_B (in = B); by streetnum streetname zip; if A and B then match = "match "; else if A then match = "A only"; else      match = "B only"; run; As for your question, you can add similar logic to SQL with a CASE statement.  Here's something like your MERGE that uses a SELECT statement since that looks a bit like a CASE statement in SQL.  Then there's an SQL equivalent. data one; input id age @@; datalines; 1 10 2 20 3 30 ; run; data two; input id zip @@; datalines; 1 12202 3 23456 4 99901 ; run; data merg; merge one (in=a) two(in=b); by id; select;    when (a and b) match = "match";    when (a)       match = "aonly";    otherwise      match = "bonly"; end; run; proc sql; create table sqel as select coalesce (one.id, two.id) as id, age, zip,        case when (one.id and two.id) then "match"             when (one.id) then "aonly"             else "bonly"        end as match from one full join two on one.id eq two.id; quit; title "MERGE"; proc print data=merg noobs; run; title "SQL"; proc print data=sqel noobs; run; MERGE id    age     zip     match 1     10    12202    match 2     20        .    aonly 3     30    23456    match 4      .    99901    bonly SQL id    age     zip     match 1     10    12202    match 2     20        .    aonly 3     30    23456    match 4      .    99901    bonly ps  from Howard Schreier's great book ... PROC SQL by Example: Using SQL within SAS https://support.sas.com/pubscat/bookdetails.jsp?pc=60500 http://howles.com/sqlbook/ ... View more

Indexes are the feature intended to fit this requirement. The insertions can also be done with PROC SQL or with a DATA step (using the MODIFY statement). Whether this approach is advantageous vis a vis appending and sorting will depend on the particulars. DLing wrote: Just a thought, if the large base dataset is indexed (create via PROC datasets), then proc datasets append statement may be able to append and update the index without physically sorting the very large tables.  The link Art provided describes appending to an indexed dataset. ... View more

Five decimal places isn't enough to reveal the drift. Try format BEST32. My statement about truncation was based on an experiment rather than on documentation. It appears to be undocumented (except for the KB article which Art referenced). I suspect that truncation has always been the behavior, but I don't have any way of testing. The documentation ought to explain this issue. I might even argue that the behavior should be changed by making the FUZZing automatic. esirjhm wrote: I don't want to quible, but your reference is from the Samples and SAS Notes Knowledge Base and was noted on SAS 8.  I'm not sure this is exactly part of the SAS official documentation.  Did this truncation only begin with SAS 8?  What about SAS 5 and SAS 6?  Where was the documentation for these versions. ... View more

Keep in mind that non-integer array indexes are truncated, not rounded. For example, MYARRAY[2.999] is equivalent to MYARRAY[2], not MYARRAY[3]. Applying the FUZZ function can correct for the "drift" caused by limited floating point precision. ... View more

Here's a PROC FREQ approach that does only one pass through the data. proc format ; value  miss_n low-high = 'N' other = 'Y' ; value $miss_c      ' ' = 'Y' other = 'N' ; run ; ods listing close ; ods output OneWayFreqs=owf ; proc freq data=x nlevels ; format _numeric_    miss_n.        _character_ $miss_c. ; tables _all_ / missing ; run ; ods output close ; ods listing ; data want (keep = Table Frequency) ; set owf ; if cats(of F_:) EQ 'Y' ; table = substr( table , index(table,' ')+1 ) ; run ; ... View more