data have; input value; cards; 371 0 145 75 40 41 19 0 10 2 0 1 3 999 0 0 0 0 0 0 0 0 0 ; run; data have; set have; if value=0 then group=0; else group=1; run; data have; set have end=last; by group notsorted; n+first.group; if last then call symputx('n',n); run; data want; set have; if n=&n and value=0 then delete; drop group n; run; ... View more

I wrote a paper at the end of the previous century with a macro to show the differences.   SUGI 23: %COMPARWS: Compare with Summary: A Macro Using Proc Compare   www2.sas.com/proceedings/sugi23/Posters/p170.pdf   sas.community.org has a zip with the programs   http://www.sascommunity.org/wiki/Macro_ComparWS_Compare_with_summary   The above assumes that both data sets have the same keys.   I wrote another macro which identifies the adds, changes, and deletes.   http://www.sascommunity.org/wiki/Macro_Extract   Ron Fehd  ... View more

Thank you art, while this method still need to merge table. ... View more

I will try this out. Thank you so much! ... View more

Best way would be do by @Reeza suggestion. But yu can achieve same by proc sql as shown below proc sql; select *, sum(dept1N) as dept1_3months_total, sum(dept2N) as dept2_3months_total, calculated dept1_3months_total+ calculated dept2_3months_total as dept1_dept2total, (calculated dept1_3months_total/calculated dept1_dept2total)*100 as mothavg from (select min(month) as min_date format =date9. from test) a cross join (select * from test)b group by ceil((intck('month', min_date, month)+1)/3); ... View more

This merge ran in 5 min on my machine. data A(keep=KEY A1-A10) B(keep=KEY B1-B10); length A1-A10 B1-B10 8; do I= 1 to 750e6; KEY=ranuni(0); if KEY < 500 / 750000 then output A; output B; end; run; data WANT; if _N_=1 then do; dcl hash A(dataset:'A'); A.definekey('KEY'); A.definedata('A1','A2','A3','A4','A5','A6','A7','A8','A9','A10'); A.definedone(); if 0 then set A; end; set B(keep=KEY B1 B2); RC=A.find(); if RC=0; run; NOTE: There were 500029 observations read from the data set WORK.A. NOTE: There were 750000000 observations read from the data set WORK.B. NOTE: The data set WORK.WANT has 500029 observations and 14 variables. NOTE: DATA statement used (Total process time):       real time           5:48.32       user cpu time       5:10.08       system cpu time     37.69 seconds       memory              71702.09k       OS Memory           98908.00k       Timestamp           23/06/2017 10:57:57 AM       Step Count                        4411  Switch Count  665   ... View more

The closest you'll get using proc sql is: select length(NUMBER,best32. -l) from HAVE; but that's a weird request, and you'll get surprises due to numeric precision as explained above. ... View more