Hi ankitsas, many thanks for this; however it only works for the country variable. The other variables are left missing (including those that initially had values) ... View more

Actually I don't think that dates should matter. There are different dates as these are seperate files downloaded together. So, the only thing that needed to be done is to fill the variable Country based on the manager number and the variables  Exchange Code, Stock Class Code, Stock Class Description and Industry Code based on the ticker symbol. I hope the above helps. Thanks, Costasg ... View more

Hi PGStats, I want to fill these variables from the start date of the database (31/12/1980) and for every observation. So COUNTRY variable should be filled according to manager number and RDATE and  Exchange Code, Stock Class Code, Stock Class Description and Industry Code variables should be filled according to ticker symbol and FDATE ... View more

Many thanks again Linlin ... View more

Thanks again Linlin. Ok, I am attaching some sample files. Data: a small sample of my data Date: a list of all dates (as not all funds trade on all dates) Funds: the codes of all funds Output: this is the final output for each stock (Ideally, I would like to keep only the new variables (s1,s2....) but rename them with the names of the old ones starting with the second date) And here again is the code I apply: proc sql; create table C as select* from work.data where stock = "ACERALIA"; quit; proc sql; create table b as select date,code,value from c; quit; proc sql; create table f as select a.*,b.* from work.Date as a left join work.b as b on a.date=b.date; quit; proc sort data=work.f nodupkey;by code date;run; data a; set f; format date monyy7.; run; proc sql; create table a_ as select distinct date,0.1 as code from a; quit; data a2; set a a_;run; proc sort data= a2; by code date;run; proc transpose data=a2 out=a3(where=(code^=0.1)); by code; id date; var value; run; data work.a4; set work.a3; drop _NAME_; run; Data old; Code=0 Mar1995=1 jun1995=2 sep1995=3 dec1995=4 mar1996=5 jun1996=6 sep1996=7 dec1996=8 mar1997=9 jun1997=10 sep1997=11 dec1997=12 mar1998=13 jun1998=14 sep1998=15 dec1998=16 mar1999=17 jun1999=18 sep1999=19 dec1999=20 mar2000=21 jun2000=22 sep2000=23 dec2000=24 mar2001=25 jun2001=26 sep2001=27 dec2001=28 mar2002=29 jun2002=30 sep2002=31 dec2002=32 mar2003=33 jun2003=34 sep2003=35 dec2003=36 mar2004=37 jun2004=38 sep2004=39 dec2004=40 mar2005=41 jun2005=42 sep2005=43 dec2005=44 mar2006=45 jun2006=46 sep2006=47 dec2006=48 mar2007=49 jun2007=50 sep2007=51 dec2007=52 mar2008=53 jun2008=54 sep2008=55 dec2008=56 mar2009=57 jun2009=58 sep2009=59 dec2009=60 mar2010=61 jun2010=62 sep2010=63 64;run; Data new; retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997 sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000 jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002 mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005 dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008 sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;set a4;run; proc sql; create table a5 as select a.*,b.* from work.funds as a left join work.new as b on a.code=b.code; quit; data asigns; set WORK.a5; array vol(63) mar1995 --sep2010; array si (62) $  si1-si62; do i= 2 to 63; if vol(i) > vol(i-1) then si(i-1)='+'; else if vol(i) <  vol(i-1) then si(i-1)='-'; else si(i-1)='&'; end; RUN; ... View more

Many thanks Linlin, it works great!!! May I ask one more question? Is there any macro that I can use to save all these files (in excel format) into one folder in my computer? Many thanks again, Costas ... View more

Thank you Linlin, The macro works, but what i would like to do is not to have to change the Stock name each time, cause there could be thousands of stocks. Is there any way that SAS can do it automatically for each stock without me changing the stock name all the time? Maybe if i create a file with all stock names and then SAS looks at this file? ... View more

Many Thanks Ksharp ... View more

Well, it is unlikely to be the problem there. I realized that the problem lies earlier, before the A3 table when I transpose the data. I have highlighted the code. Any suggestions? proc sql; create table f as select a.*,b.* from work.Date as a left join work.b as b on a.date=b.date; quit; proc sort data=work.f nodupkey;by date code;run; data a; set f; format date monyy7.; run; proc sql; create table a_ as select distinct date,0.1 as code from a; quit; data a2; set a a_;run; proc sort data= a2; by code date;run; proc transpose data=a2 out=a3(where=(code^=0.1)); by code; id date; var value; run; data work.a4; set work.a3; drop _NAME_; run; proc sql; create table a5 as select a.*,b.* from work.funds as a left join work.a4 as b on a.code=b.code; quit; proc freq data=a5;run; data asigns; set WORK.a5; array vol(63) mar1995 --sep2010; array si (62) $  si1-si62; do i= 2 to 63; if vol(i) > vol(i-1) then si(i-1)='+'; else if vol(i) <  vol(i-1) then si(i-1)='-'; else si(i-1)='&'; end; RUN; ... View more

well actually the problem is that one variable (SEP2010) is in the wrong order, instead of last one is 8th. I am attaching the output. ... View more

I don't have words to thank you for your help and time. I will check it in a few days that I will return to the uni but I am sure it is correct. Many thanks again ... View more

I am attaching  a sample file. I did the calculations for the first months. Many thanks ... View more