Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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

       


Accepted Solutions
Solution
‎03-11-2017 07:35 PM
Valued Guide
Posts: 797

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

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;

View solution in original post


All Replies
Solution
‎03-11-2017 07:35 PM
Valued Guide
Posts: 797

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

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;

New Contributor
Posts: 2

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

Thanks, it worked fine!!!

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

Valued Guide
Posts: 797

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 239 views
  • 0 likes
  • 2 in conversation