Thank you for this Kurt, the problem is that people are keying into spreadsheets which then get inputted so we need to use all their fields to calc some elements, hence your transpose to a long file does the job perfectly bringing it in.    I am now thinking I will export the results out and then use an excel macro to convert them back to the way they see them currently to avoid transposing it back again in SAS ... View more

Hi Tom,   I tried using your code which gave me the first bal_202010 result but thereafter each of the results was a missing calculation. Here is the code I ran (slight tweak as I want the first result to be ledgerbal / 1000000 + the same month's result of cinr. The next calc should be the result of what we have just created + cinr_202011 etc. but all come back as missing even though there are numeric values in there :   data closing_bals_calcz; set closing_bals_calc; array bal bal_202010 bal_202011 bal202012 bal_202101; array cinr cinr_202010 cinr_202011 cinr_202112 cinr_202101 ; do index=1 to dim(bal); if index=1 then bal[index] = ledgerbal_Oct20 / 1000000+cinr[index]; else bal[index]=bal[index] + cinr[index]; end; run;     ... View more

Tom,    Thank you for this as meant I didn't have to do a long winded keep statement every time. Is there a way for me to then use these names within a calculation.    I have attached a mini version of the closing_bals_tester dataset and attached it as a csv.   I want to be able to do the big calculation below but struggling as obviously I have created some in the names piece. How would I macro or loop this? Apologies for asking you as a reply but I just wondered if I could use some of the elements created in the earlier macro.   %let fstmonth = '31Oct2020'd; %let lstmonth = '31Dec2025'd; data names ; length start end offset date 8 name $32.; format stat end date yymmn6.; start=&fstmonth; end=&lstmonth; do offset=0 to intck('month',start,end); date = intnx('month',start,offset); name=cats('cinr_',put(date,yymmn6.)); output; end; run; proc sql noprint; select name into :names separated by ' ' from names; quit; data test; set ciandnr1 (keep = brand no1 product_group &names); run; /*Take the last actual closing balance */ data Last_Actual_Product_Balances; set sav.sepoct20 (keep=ProdRepGroup prodrepgroupid LedgerBal_oct20); run; /*Merge the tables */ proc sql; create table closing_bals_calc as select a.*, b.* from Last_Actual_product_Balances as a left join test as b on a.prodrepgroupid=b.no1; quit; /* Now do the calcs which should be closing balance for the last actual month and the rest are the result of the prior month plus the ci and net receipts- we need to put something in to only run this for the months we want to choose as we did in earlier macro */ data closing_bals_calca; set closing_bals_calc; bal_202010 = ledgerbal_Oct20 / 1000000; bal_202011 = bal_202010 + cinr_202011; bal_202012 = bal_202011 + cinr_202012; bal_202101 = bal_202012 + cinr_202101; bal_202102 = bal_202101 + cinr_202102; bal_202103 = bal_202102 + cinr_202103; bal_202104 = bal_202103 + cinr_202104; bal_202105 = bal_202104 + cinr_202105; bal_202106 = bal_202105 + cinr_202106; bal_202107 = bal_202106 + cinr_202107; bal_202108 = bal_202107 + cinr_202108; bal_202109 = bal_202108 + cinr_202109; bal_202110 = bal_202109 + cinr_202110; bal_202111 = bal_202110 + cinr_202111; bal_202112 = bal_202111 + cinr_202112; bal_202201 = bal_202112 + cinr_202201; bal_202202 = bal_202201 + cinr_202202; bal_202203 = bal_202202 + cinr_202203; bal_202204 = bal_202203 + cinr_202204; bal_202205 = bal_202204 + cinr_202205; bal_202206 = bal_202205 + cinr_202206; bal_202207 = bal_202206 + cinr_202207; bal_202208 = bal_202207 + cinr_202208; bal_202209 = bal_202208 + cinr_202209; bal_202210 = bal_202209 + cinr_202210; bal_202211 = bal_202210 + cinr_202211; bal_202212 = bal_202211 + cinr_202212; bal_202301 = bal_202212 + cinr_202301; bal_202302 = bal_202301 + cinr_202302; bal_202303 = bal_202302 + cinr_202303; bal_202304 = bal_202303 + cinr_202304; bal_202305 = bal_202304 + cinr_202305; bal_202306 = bal_202305 + cinr_202306; bal_202307 = bal_202306 + cinr_202307; bal_202308 = bal_202307 + cinr_202308; bal_202309 = bal_202308 + cinr_202309; bal_202310 = bal_202309 + cinr_202310; bal_202311 = bal_202310 + cinr_202311; bal_202312 = bal_202311 + cinr_202312; bal_202401 = bal_202312 + cinr_202401; bal_202402 = bal_202401 + cinr_202402; bal_202403 = bal_202402 + cinr_202403; bal_202404 = bal_202403 + cinr_202404; bal_202405 = bal_202404 + cinr_202405; bal_202406 = bal_202405 + cinr_202406; bal_202407 = bal_202406 + cinr_202407; bal_202408 = bal_202407 + cinr_202408; bal_202409 = bal_202408 + cinr_202409; bal_202410 = bal_202409 + cinr_202410; bal_202411 = bal_202410 + cinr_202411; bal_202412 = bal_202411 + cinr_202412; bal_202501 = bal_202412 + cinr_202501; bal_202502 = bal_202501 + cinr_202502; bal_202503 = bal_202502 + cinr_202503; bal_202504 = bal_202503 + cinr_202504; bal_202505 = bal_202504 + cinr_202505; bal_202506 = bal_202505 + cinr_202506; bal_202507 = bal_202506 + cinr_202507; bal_202508 = bal_202507 + cinr_202508; bal_202509 = bal_202508 + cinr_202509; bal_202510 = bal_202509 + cinr_202510; bal_202511 = bal_202510 + cinr_202511; bal_202512 = bal_202511 + cinr_202512; run;   Appreciate any help / guidance   David ... View more