topic Re: is there a way of creating a loop to do the same work this code is doing for 187 firms? in SAS Procedures

is there a way of creating a loop to do the same work this code is doing for 187 firms?

sasnewuser1 — Sat, 11 Mar 2017 00:25:51 GMT

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

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

Re: is there a way of creating a loop to do the same work this code is doing for 187 firms?

mkeintz — Sat, 11 Mar 2017 01:20:34 GMT

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;

Re: is there a way of creating a loop to do the same work this code is doing for 187 firms?

sasnewuser1 — Sun, 12 Mar 2017 00:35:33 GMT

Thanks, it worked fine!!!

Is it possible to explain how the arrays' part work in this code?

Re: is there a way of creating a loop to do the same work this code is doing for 187 firms?

mkeintz — Sun, 12 Mar 2017 16:37:41 GMT

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.