I have my data in the excel sheet as the following: company_ticker_1 date_1 price_1 return_1 dividend_1 ( ) ....company_ticker_187 date_187 price_187 return_187 dividend_187 1 12 0 0 ( ) ....187 9 13 2 1 12 0 0 ( ).... 187 9 0 0 1 12 0 0 ( ).... 187 10 11 0 and i want to convert my data to this format company_ticker date price return dividend 1 12 0 0 1 12 0 0 1 12 0 0 2 3 8 0 2 3 0 0 ... 187 10 11 0 I tried to do it for the two company's with this code: proc sql; CREATE table data as SELECT Company_Ticker,Date,PX_LAST,DAY_TO_DAY_TOT_RETURN_GROSS_DVDS,EQY_DPS FROM test where date is not null; quit; proc sql; CREATE view new_view_1 AS SELECT Company_Ticker_1 as Company_Ticker , Date_1 as date,PX_LAST_1 as PX_LAST , DAY_TO_DAY_TOT_RETURN_GROSS_DVD1 as DAY_TO_DAY_TOT_RETURN_GROSS_DVDS,EQY_DPS_1 as EQY_DPS FROM test where Date_1 is not null; quit; proc append base=data data=new_view_1 force;run; proc sql; CREATE view new_view_2 AS SELECT Company_Ticker_2 as Company_Ticker , Date_2 as date,PX_LAST_1 as PX_LAST , DAY_TO_DAY_TOT_RETURN_GROSS_DVD2 as DAY_TO_DAY_TOT_RETURN_GROSS_DVDS,EQY_DPS_2 as EQY_DPS FROM test where Date_2 is not null; quit; proc append base=data data=new_view_2 force;run; proc sql; CREATE view new_view_3 AS SELECT Company_Ticker_1 as Company_Ticker , Date_3 as date,PX_LAST_3 as PX_LAST , DAY_TO_DAY_TOT_RETURN_GROSS_DVD1 as DAY_TO_DAY_TOT_RETURN_GROSS_DVDS,EQY_DPS_3 as EQY_DPS FROM test where Date_3 is not null; quit; proc append base=data data=new_view_3 force;run; Can we create a loop that does the job i'm repeating 9in this case for 3 companies but i have to dit for more)? Or is there even a better way to do it? Thank you all
... View more
