Hello all,   I have a database, it has content like this data temp.database ; input STORE YEAR SALE ID; datalines; 001 2001 12 1212 001 2002 12 1212 001 2003 12 1212 002 2001 12 1212 002 2002 12 1212 002 2003 12 1212 003 2001 12 1216 003 2002 12 1216 003 2003 12 1216 004 2001 12 1216 004 2002 12 1216 004 2003 12 1216 005 2001 12 1216 005 2002 12 1216 005 2003 12 1216 006 2001 12 1212 006 2002 12 1212 006 2003 12 1212 007 2001 12 1212 007 2002 12 1212 007 2003 12 1212 008 2001 12 1219 008 2002 12 1219 008 2003 12 1219 009 2001 12 1219 009 2002 12 1219 009 2003 12 1219 010 2001 12 1219 010 2002 12 1219 010 2003 12 1219; run;   Firrst, I am not looking for all the STORE in the database, I only care about the STORE I am interested in, the interested STORE list is like this data maps.Storelist ; input STORE; datalines; 002 005 008 ; run; Firrst, I want to SUM all the SALE (let's call it SUMSALE) under the same ID and same YEAR. Then, I want the SALE of each STORE of each YEAR in the Storelist. (This is directly given in the Database) Then SALE/SUMSALE as SHARE.   My code is like this, proc sql; create table want as select Database.STORE, Database.YEAR, Database.SALE, Database.ID, sum(Database.SALE) as SUMSALE, Database.SALE / SUMSALE as SHARE /* This is the part I am not clear. Can I directly use the new variable?*/ from temp.database,maps.Storelist where Storelist.ID=Database.ID group by Database.SALE, Database.ID order by Database.STORE,Database.YEAR; quit; But SAS says the SHARE variable is not found in the contributing tables.   Can I directly use the variable I am creating now in the next step calculation? Would this be solved by this? Database.SALE /sum(Database.SALE)as SHARE   Thank you all.   Best wish.     ... View more

Hello all,   I have a thousand dataset, and each of them like this:   data have ; input YEAR SALE ID $5.; datalines;2001 12 ab34 2001 10 1234 2001 14 ab34 2001 10 2234 2002 10 1234 2002 10 ab34 2002 10 2234 2002 10 2234 2001 12 ab34 2001 10 1234 2001 14 2234 2001 10 2234 2002 10 ab34 2002 10 1234 2002 10 ab34 2002 10 2234; run;   For each dataset, I would like to sum all the SALE winthin same YEAR whose ID start with a letter, and sum the SALE within same YEAR only. I know how to run this with the following code. Thanks to those guys in my another post proc sql; create table want as select YEAR, sum(SALE*anyalpha(first(ID))) as SALE1, sum(SALE) as TOTALSALE from have group by YEAR; quit; _But how can I do this for each of 1000 dataset. It is impossible to do this manually. Really urgent. How can I repeat this using MACRO.   Thank you!  ---------------------------------------------------------------------------------------------------updated 02-28-2018 I figure it out guys!!!!!!!!!!!!! This is the code I use: %macro doit(memname); proc sql; create table want.&memname as select YEAR, sum(SALE*anyalpha(first(ID))) as SALE1, sum(SALE) as TOTALSALE, from have.&memname group by YEAR; quit; %mend doit; data _null_; set work.member; /* this is a list I created before hand, it contains all the member name of the datasets I have*/ call execute('%doit('!!memname!!');'); run; Now I have the variables I want, but they all in individual dataset. So the question now is how can I add another variable for each dataset accoeding to its file name? ... View more