@cneed: As a simple remedy, you could just insert DISTINCT between SELECT and C.studentID, and you would not have the dupes. The query offered by @PGStats results in the same (in his query, though, SUM should be replaced by COUNT, or else it won't work). However, neither is efficient. It makes more sense to aggregate the Grades file first,  which will collapse it to unique StudentID key-values, and then join the result with Master: proc sql ; create table data.master_after2001withgrades as select m.studentid , m.birthdate , m.gender , m.schoolid , m.schoolname , g.grade_count from data.master_after2001 c left join (select studentid , count (grade) as grade_count from grades group studentid ) g on m.studentid = g.studentid ; quit ; Alternatively, it can be achieved by applying the same concept by first aggregating Grades via a hash table: data want (drop = grade) ; if _n_ = 1 then do ; dcl hash h () ; h.definekey ("studentid") ; h.definedata ("grade_count") ; h.definedone () ; do until (z) ; set grades end = z ; if h.find() ne 0 then grade_count = 1 ; else grade_count + 1 ; h.replace() ; end ; end ; set master (keep = studentid birthdate gender schoolid schoolname) ; if h.find() ne 0 then call missing (grade_count) ; run ; Neither SQL nor hash require the input data sets to be sorted by StudentID. However, if they are already sorted, the easiest and fastest way of getting what you want is a simple merge: data want (drop = grade) ; do until (last.studentid) ; merge master (keep = studentid birthdate gender schoolid schoolname) grades (in = g) ; by studentid ; if g then grade_count = sum (grade_count, 1) ; end ; run ; Of course, there're other ways of achieving the same result using more than a single step.    Kind regards Paul D.  ... View more

@jimmychoi: The concept is the same as @novinosrin's, just the mechanism of resurrecting the variables after they are nulled is a bit different: data have ; input (company_name rd mfg failed) ($) ; cards ; 1abtik RD.PRO . F.PRO Oncol . M.PRO F.PRO Thera RD.PRO . . Group RD.PRO M.PRO . ; data want (drop = _:) ; set have ; array cc rd mfg failed ; do over cc ; _t = cc ; call missing (of cc[*]) ; cc = _t ; if not cmiss (cc) then output ; set have point = _n_ ; end ; run ; Kind regards Paul D. ... View more

@abdulla: Assuming that the input is sorted by [gvkey,year], you can interleave the files by this key. Then in the first pass through each BY group do the summation and in the second pass decide whether to keep the group and if yes, compute the percentage for the record with rank=1. In SAS words: data have ; input rank tdc1 gvkey year ; cards ; 1 5237.743 1004 2010 2 2554.467 1004 2010 3 1578.104 1004 2010 5 1314.079 1004 2010 4 1596.902 1004 2010 1 5786.4 1004 2011 2 2781.156 1004 2011 4 1696.431 1004 2011 3 1727.069 1004 2011 1 4182.832 1004 2012 2 2068.554 1004 2012 5 841.204 1004 2012 3 1312.852 1004 2012 4 1131.176 1004 2012 1 3700.925 1013 2010 2 1099.786 1013 2010 5 848.657 1013 2010 3 1067.566 1013 2010 4 941.748 1013 2010 1 5231.393 1045 2010 4 2372.14 1045 2010 5 1776.069 1045 2010 3 2288.908 1045 2010 2 3142.082 1045 2010 ; run ; data want (drop = _:) ; set have (in=h) have ; by gvkey year ; if h then do ; if first.year then call missing (_s, _n) ; _s + tdc1 ; _n + 1 ; end ; else if _n = 5 then do ; if rank = 1 then tdc1_pct = divide (tdc1, _s) ; output ; end ; run ; Kind regards Paul D.  ... View more

@Solly7: Methinks the simplest thing logically is: Store the records with the positive amounts in a search table by policy and amount Read the file one record at a time. If amount is positive, output the record. Otherwise see if the absolute value of amount for the current value of policy is in the table. If it is, output the record and remove the respective item from the table to prevent another output for the same policy and negative amount downstream. Extra advantages of the approach (besides the logical): it works regardless of the input file's record order (sorted or disordered) it requires less than 2 passes through the input data . In SAS words:   data have ; input policy_no amount ; cards ; 3 -700 1 500 2 -1000 1 -500 3 700 1 -500 run ; data want ; if _n_ = 1 then do ; dcl hash h (dataset:"have (where = (amount > 0))") ; h.definekey ("policy_no", "amount") ; h.definedone () ; end ; set have ; if amount > 0 then output ; else if h.check (key:policy_no, key:-amount) = 0 then do ; output ; h.remove (key:policy_no, key:-amount) ; end ; run ; Kind regards Paul D. ... View more

@CathyVI: Do not use proc IMPORT. Use the DATA step: It has no objections to any extension and will read the file as long as it is readable. For example, I've created a test text file with 2 records, renamed it using the .dat extension, and, lo and behold: 1 filename in "C:\Users\sashole\Documents\test\test.dat" ; 2 3 data _null_ ; 4 infile in ; 5 input ; 6 put _infile_ ; 7 run ; NOTE: The infile IN is: Filename=C:\Users\sashole\Documents\test\test.dat, RECFM=V,LRECL=32767,File Size (bytes)=22, Last Modified=14Jan2020:17:23:56, Create Time=14Jan2020:17:23:56 1234567890 XYZABCQWER NOTE: 2 records were read from the infile IN. Kind regards Paul D.   ... View more

@Tom: > Why do you think the hash tables will be faster than PROC SQL? < A good question if the OP's idea is to make it run faster rather than some other motive.    > How do you know that PROC SQL is not already doing a hash? < SAS SQL never uses the SQXJHSH access method for any join but an inner join and cannot be forced to even via MAGIC=103 option. I don't know why since algorithmically it's not a problem (e.g., it's easy to do a left/right join using the hash object).    > Are the two tables small enough that you can load them into memory that a hash table would require? < It very rarely a problem, and when it is due to the frontal attack approach (i.e. load all the keys and all the data), in the case of a join it can always be circumvented by storing only the keys (or their combined MD5 signature if the composite key is really long) in the key portion and the record number RID in the data portion. Then the rest of the data can be extracted via POINT=RID  if there's a key match. There're other ways of dealing with memory limitations as well. @DonH and I have a whole chapter on the topic in our "hash book":   https://www.sas.com/store/books/categories/examples/data-management-solutions-using-sas-hash-table-operations-a-business-intelligence-case-study/prodBK_69153_en.html    In really extreme cases (which arise primarily when attacking massive data aggregation), this always works:   https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/1755-2018.pdf    Kind regards Paul D.                 ... View more

@SASKiwi: Without a sense of humor it would be easy to go cuckoo. Macro abuse is pretty ubiquitous but in certain industries it verges on insane. Tons of "code" concocted in this fashion I've had to protrude through often made me think that those writing it believe that if a piece of code is not encapsulated in a macro and then called as such it just won't run.   Kind regards Paul D.   ... View more

@Rakesh93: If you don't mind guessing the number of needed columns beforehand, the algorithm is simple: data have ; input txt $80. ; cards ; The time has come, the Walrus said, to talk of many things: Of shoes and ships and sealing-wax, of cabbages and kings And why the sea is boiling hot and whether pigs have wings ; run ; %let w = 20 ; * change to 200 in your case ; data want (drop = _:) ; set have ; array c $ &w col1-col5 ; _i_ = 1 ; do _x = 1 to countw (txt, "") ; c = catx (" ", c, scan (txt, _x, "")) ; if length (c) <= &w and length (c) + length (scan (txt, _x + 1)) > &w or _x = countw (txt, "") then _i_ + 1 ; end ; run ; If you want it to be truly dynamic, run the same exact algorithm twice: (1) to determine the actual dimension of array C and (2) to produce the final output, e.g.: data _null_ ; set have end = z ; length c $ &w ; do _x = 1 to countw (txt, "") ; c = catx (" ", c, scan (txt, _x, "")) ; if length (c) <= &w and length (c) + length (scan (txt, _x + 1)) > &w or _x = countw (txt, "") then do ; _i_ = sum (_i_, 1) ; call missing (c) ; end ; end ; last._i_ = max (last._i_, _i_) ; if z then call symputx ("d", last._i_) ; run ; data want (drop = _:) ; set have ; array c $ &w col1-col&d ; _i_ = 1 ; do _x = 1 to countw (txt, "") ; c = catx (" ", c, scan (txt, _x, "")) ; if length (c) <= &w and length (c) + length (scan (txt, _x + 1)) > &w or _x = countw (txt, "") then _i_ + 1 ; end ; run ; You can massage it if you wish to encapsulate the repetitive code.    Kind regards Paul D.   ... View more