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

[sasnewuser1]

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

[mkeintz · Accepted solution]

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;

[sasnewuser1]

Thanks, it worked fine!!!

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

[mkeintz]

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.