Hi.   Not totally sure of how this should working, as there seem to be some missing pieces...   For example, total Money_Dpt sums 1300, and you have 1800 in table FOND to distribute...   Anyway, here's a try that will load the DPT table into an hash table (in memory) and work from there to distribute the amounts of in the FOND table.     data WANT; set FOND; keep FOND Money Cumulative_Money Dept; * keep result vars; if 0 then set DEPS; * merge table layouts; if _N_ eq 1 then do; declare hash HD(dataset: 'DPT'); _RC = HD.defineKey('Dept'); _RC = HD.defineData(all:'yes'); HD.defineDone(); * hash to load Depts; declare hiter HI('HD'); * iterator to traverse it; end; _Money=0; _RC=HI.first(); * get first Dept in iterator; do until (HI.next()); if Money gt Money_Dpt then do; * check if enough; _Money=Money-Money_Dpt; Money=Money_Dpt; end; else _Money=0; * enough, set 100%; if Money gt 0 then output; * if done, output; Money_Dpt+(-Money); * adjust amount available for Dept; Money=_Money; * adjust to remaining money to fund; _RC = HD.replace(); * replace amount available for Dept; end; run;   This will get you the following for the example you provided:   FOND    Money  Money  Dept Fond_1  500    500    D_1 Fond_2  100    800    D_1 Fond_2  200    800    D_2 Fond_3  500    1800   D_2   Of course, Fond_3 gets only 500 of the (1000 needed) from D_2, since there is no more Money there.   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more

@RW9 code is the right answer to your question.   If you want m to be a char variable instead, apply the put function to the result like below:   data want; set have; m=put(month(input(cats('01',month,'2010'),date9.)),z2.); run;   Danile Santos @ www.cgd.pt ... View more

Hi.   Yes, you've got it right.   We had the same problem here with DB2, which was solved by recreating the DB2 sequence in SAS throught a TRANTAB.   Also, using non pass-through SQL won't guarantee that the data is sorted on the SAS side.   The SAS/Access engine might push it to the database.   You can check what is running and where by activating the SASTRACE option like this:   options sastrace=',,,d' sastraceloc=saslog;   If you see an "order by" being pass, then it might be the problem... If not, then it's something else.   Which DB system are you using? DB2/LUW or not?   Daniel Santos @ www.cgd.pt ... View more

OK. I've modified the macro to get you what you want, as long as you don't mind a different order: %macro doit(IN_W,IN_B,B,W,OUT); proc sql noprint; create table &OUT (drop = _:) as select distinct * from %do I=1 %to &B; &IN_B (keep=BLACK_NAME BLACK_VALUE rename=(BLACK_NAME=B&I._NAME BLACK_VALUE=B&I._VALUE)) as B&I,%end; %do J=1 %to &W; &IN_W (keep=WHITE_NAME WHITE_VALUE rename=(WHITE_NAME=W&J._NAME WHITE_VALUE=W&J._VALUE)) as W&J,%end; &IN_W (obs=1 keep=WHITE_NAME rename=(WHITE_NAME=_DUMMY)) as _DUMMY where %do I=1 %to %eval(&B-1); B&I._NAME lt B%eval(&I+1)_NAME and %end; %do J=1 %to %eval(&W-1); W&J._NAME lt W%eval(&J+1)_NAME and %end; 1; quit; %mend doit; %doit(WHITE,BLACK,2,2,WANT); * in_white, in_black, white, black, out; The cartesian product will produce the permutations following the rules (where condition) of the results you need. For example for B=2, W=2 the macro will build the following SQL statement:   select distinct * from BLACK (keep=BLACK_NAME BLACK_VALUE rename=(BLACK_NAME=B1_NAME BLACK_VALUE=B1_VALUE)) as B1, BLACK (keep=BLACK_NAME BLACK_VALUE rename=(BLACK_NAME=B2_NAME BLACK_VALUE=B2_VALUE)) as B2, WHITE (keep=WHITE_NAME WHITE_VALUE rename=(WHITE_NAME=W1_NAME WHITE_VALUE=W1_VALUE)) as W1, WHITE (keep=WHITE_NAME WHITE_VALUE rename=(WHITE_NAME=W2_NAME WHITE_VALUE=W2_VALUE)) as W2 where B1_NAME lt B2_NAME and W1_NAME lt W2_NAME Hope it helps. Daniel Santos @ www.cgd.pt ... View more

Hi.   Not sure of what's happening there, but here it cycles quite perfectly.   create table historicalmatch2010 as select unique t1.ORIACCTN, case when t8.report = "20100331" then t8.reporter end as Q12010, case when t8.report = "20100630" then t8.reporter end as Q22010, case when t8.report = "20100930" then t8.reporter end as Q32010, case when t8.report = "20101231" then t8.reporter end as Q42010, case when t8.lnacctno is missing then 0 else 1 end as Match2010 from out.APPROVED_AVLENDCD_CONDENSED t1 left join iif.iif2010 t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno) ... create table historicalmatch2016 as select unique t1.ORIACCTN, case when t8.report = "20160331" then t8.reporter end as Q12016, case when t8.report = "20160630" then t8.reporter end as Q22016, case when t8.report = "20160930" then t8.reporter end as Q32016, case when t8.report = "20161231" then t8.reporter end as Q42016, case when t8.lnacctno is missing then 0 else 1 end as Match2016 from out.APPROVED_AVLENDCD_CONDENSED t1 left join iif.iif2016 t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno) Daniel Santos @ www.cgd.pt ... View more

Hi.   Could be done with a custom informat and arrays.    proc format;      invalue code (upcase)              'NOT AT ALL'=0              'LITTLE BIT'=1              'SOMEWHAT'=2              'QUITE A BIT'=3              'VERY MUCH'=4              other=-1; run;   data want;      set have;      array Q {38} Q1-Q38;      array R {38} R1-R38;      do i = 1 to dim(Q);         R(i)=input(Q(i),code.);      end; run;   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more