Hi @TrueTears  While I am not sure and of course can't gauge what your hardware capacity is- like RAM, OS, disk space and other factors for solutions that may have higher dependency on the mentioned factors , for what it's worth I am offering a solution using HASH. The solution may or may not be fast as I haven't really tested extensively, however it adds to the menu offering more choices for you to play with.   Your total_id being unique across observations,  makes it a very convenient look up operation. Therefore, if I understand your requirement thoroughly, the logic is fairly straight forward regardless of the approach be it SQL or Datastep. Secondly, the TREAT variable being binary, offers the convenience of splitting the observations into a base table and a look up table.  Well, the rest is a breeze IMHO> DATA have; INPUT Person_ID Total_ID Treat ps_score; CARDS; 1004 1 0 0.01 1004 2 0 0.03 1004 3 1 0.043 1004 4 1 . 1004 5 1 0.02 1004 6 0 . 1007 7 0 . 1007 8 1 0.032 1007 9 1 0.77 1007 10 0 0.041 1009 11 0 0.045 1009 12 0 0.11 1009 13 0 0.82 1009 14 1 0.94 ; RUN; data temp; if _n_=1 then do; if 0 then set have have(rename=(person_id=_person_id Total_ID=Total_ID_ ps_score=_ps_score)); dcl hash h(dataset:'have(where=(treat=0 and _ps_score) rename=(person_id=_person_id Total_ID=Total_ID_ ps_score=_ps_score)))'); h.defineKey('Total_ID_'); h.definedata('_Person_ID','_ps_score','Total_ID_'); h.defineDone(); dcl hiter hi('h'); end; do until(z); set have end=z; array t(9999)_temporary_; call missing(of t(*),__n,Total_ID_); if not(treat and ps_score) then do; output; continue; end; _n_=constant('big'); do while(hi.next()=0); if person_id=_person_id then continue; _n=round(abs(_ps_score-ps_score),0.0001); if _n<=_n_ then do; if _n<_n_ then call missing(of t(*),__n); _n_=_n; __n=sum(__n,1); t(__n)=Total_ID_; end; end; do _n_=1 by 1 while(t(_n_)); _rc=h.find(key:t(_n_)); output; end; end; drop _:; run; /*Transpose to wide to meet your expected output*/ proc transpose data=temp out=want(drop=_name_) prefix=matched_ID_; by person_id--ps_score; var Total_ID_; run; proc print noobs;run;   Person_ID Total_ID Treat ps_score matched_ID_1 matched_ID_2 1004 1 0 0.010 . . 1004 2 0 0.030 . . 1004 3 1 0.043 10 11 1004 4 1 . . . 1004 5 1 0.020 10 . 1004 6 0 . . . 1007 7 0 . . . 1007 8 1 0.032 2 . 1007 9 1 0.770 13 . 1007 10 0 0.041 . . 1009 11 0 0.045 . . 1009 12 0 0.110 . . 1009 13 0 0.820 . . 1009 14 1 0.940 10 . ... View more

Try this: proc sql; create table template as select distinct a.person_id, a.item_id, a.item_name, a.person_name, b.year from have a, have b where a.person_id = b.person_id ; create table want as select a.person_id, a.item_id, a.year, case when b.amount = . then 0 else b.amount end as amount, a.person_name, a.item_name, b.document_id from template a left join have b on a.person_id = b.person_id and a.item_id = b.item_id and a.year = b.year ; quit; ... View more

Use arrays:   data want; array lat_ latitude1-latitude99; array lon_ longitude1-longitude99; array dist_ distance1-distance99; set myDataset; do i = 1 to dim(dist_); dist_{i} = geodist(latitude, longitude, lat_{i}, lon_{i}); end; drop i; run; (untested) ... View more

@TrueTears  Ideally you try and understand the code to a level where you can make such a change yourself. But here you go. data want(keep=year firm_id city_id operate action action_adj); if _n_=1 then do; if 0 then set list; dcl hash list(dataset:'list(where=(use=1))', multidata:'y'); list.defineKey('city_id'); list.defineData('city_id_adj'); list.defineDone(); dcl hash Operate0(dataset:'have(keep=year city_id operate where=(operate=0))'); Operate0.defineKey('year','city_id'); Operate0.defineDone(); dcl hash Action1(dataset:'have(keep=year city_id action where=(action=1))'); Action1.defineKey('year','city_id'); Action1.defineDone(); end; call missing(of _all_); set have; /* If operate = 0, then action_adj = 3 */ if operate = 0 then do; action_adj = 3; output; return; end; /* For each city_id in have, we check its corresponding city_id_adj in dataset "list" that has use=1 */ if list.check()=0 then do; /* If in this set of city_id_adj, there is one that has operate = 0 in the dataset "have", then action_adj = 2 */ list.reset_dup(); do while(list.do_over()=0); if Operate0.check(key:year, key:city_id_adj)=0 then do; action_adj = 2; output; return; end; end; /* action_adj = 1 if there is at least one city_id_adj (with use=1) that has action = 1 in the dataset "have". */ list.reset_dup(); do while(list.do_over()=0); if Action1.check(key:year, key:city_id_adj)=0 then do; action_adj = 1; output; return; end; end; end; /* action_adj = 0 for all other cases */ action_adj = 0; output; return; run; ... View more

Proc SQL is well suited for this operation:   proc sql; create table want as select a.*, case when a.shock then "T" when max(c.shock) then "A" else "N" end as treat from have as a left join list as b on a.id=b.id left join have as c on b.id_adj=c.id and a.year=c.year group by a.year, a.id, a.shock; quit; ... View more

data have; input year ID $ shock; cards; 2000 1001 1 2001 1001 1 2002 1001 1 2003 1001 1 2004 1001 1 2000 1003 1 2001 1003 1 2002 1003 1 2003 1003 1 2004 1003 1 2000 1007 1 2001 1007 1 2002 1007 1 2003 1007 1 2004 1007 1 2000 1010 1 2001 1010 1 2002 1010 1 2003 1010 1 2004 1010 1 ; run; data list; input ID $; cards; 1001 1003 1004 1005 1007 1009 1010 ; run; data want; merge have(in=a) list(in=b); by id; if a then output; else if b then do; shock=0; do year=2000 to 2004;; output; end; end; run; ... View more

Yes, that would mess things up! Try:   data somename; set somename(rename=( t_b_&input2.5=&input2 )); run; Art, CEO, AnalystFinder.com ... View more

Sure, PROC SQL can do the trick, but PROC MEANS (or PROC SUMMARY for that matter) can compute more statistics than PROC SQL, e.g. skewness (thanks, @Reeza, for the hint!). Please note that your S statistic can be derived from the coefficient of variation: S=100/CV. Your g statistic can be calculated directly as skewness with the VARDEF=N option of the PROC MEANS statement, whereas your sample standard deviation would require VARDEF=DF (for the denominator n-1, which I assume is what you want). Instead of merging two datasets with summary statistics (one for each setting of VARDEF), I decided to convert the default "DF skewness" to "N skewness" by multiplying with the appropriate conversion factor f := (n-1)(n-2)/n² = 1 - 3/n + 2/n².     proc summary data=bootsample; by replicate; var a b c d; output out=stats(drop=_:) cv= skew= / autoname; run; %let f=%sysevalf(1-3/&record_count+2/&record_count**2); data want; set stats; a_t=100/a_CV+&f*a_Skew; b_t=100/b_CV+&f*b_Skew; c_t=100/c_CV+&f*c_Skew; d_t=100/d_CV+&f*d_Skew; drop a_CV--d_Skew; run; ods html file="C:\Temp\t_stat.html"; ods listing close; title 'The t Statistic'; proc print data=want label noobs; run; ods html close; ods listing; title;   This could be the PROC SQL code (perhaps for validation purposes or for comparison of run times and numerical accuracy): proc sql; create table want as select replicate, m_a/s_a+sum(x_a)/(&record_count*s_a**3) as a_t, m_b/s_b+sum(x_b)/(&record_count*s_b**3) as b_t, m_c/s_c+sum(x_c)/(&record_count*s_c**3) as c_t, m_d/s_d+sum(x_d)/(&record_count*s_d**3) as d_t from (select replicate, mean(a) as m_a, std(a) as s_a, (a-calculated m_a)**3 as x_a, mean(b) as m_b, std(b) as s_b, (b-calculated m_b)**3 as x_b, mean(c) as m_c, std(c) as s_c, (c-calculated m_c)**3 as x_c, mean(d) as m_d, std(d) as s_d, (d-calculated m_d)**3 as x_d from bootsample group by replicate) group by replicate; quit; Minor differences (like 1E-13, but depending on your a, b, c, d values) between the two approaches are likely to occur.   ... View more

proc sql noprint; select count(*) into :record_count from c; quit; %put &record_count; proc surveyselect data=a out=b seed = 1234 method = urs sampsize=&record_count /* This is the number of rows of a dataset called C, which has already been created*/ outhits rep = 1; run; ... View more