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
This can probably be done with a lot less coding using the data step:
data want (keep=company_ticker date price return dividend);
set have;
array ct {187} company_ticker_1-company_ticker_187;
array dt {187} date_1-date_187;
array p {187} price_1-price_187;
array ret {187} return_1-return_187;
array div {187} dividend_1-divident_187;
do I=1 to 187;
company_ticker=ct{I};
date=date{I};
price=p{i};
return=ret{i};
dividend=div{i};
output;
end;
run;
This can probably be done with a lot less coding using the data step:
data want (keep=company_ticker date price return dividend);
set have;
array ct {187} company_ticker_1-company_ticker_187;
array dt {187} date_1-date_187;
array p {187} price_1-price_187;
array ret {187} return_1-return_187;
array div {187} dividend_1-divident_187;
do I=1 to 187;
company_ticker=ct{I};
date=date{I};
price=p{i};
return=ret{i};
dividend=div{i};
output;
end;
run;
Thanks, it worked fine!!!
Is it possible to explain how the arrays' part work in this code?
Do a google search for
sas samples arrays
and you'll see lots of likely explanatory documents.
The one at the top of my google results is
Arrays Made Easy: An Introduction to Arrays and Array Processing
And there are plenty more.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.