SAS data step and macros

[shyamili]

Hi. I am new to SAS and have a doubt, which might sound basic to many ppl here.

I have a data set ABC which is like, lets say

a               b    

____________

compa1     compb1

compa1     compb2

compa1     compb3

compa2     compb1

compa2     compb2

For each row, I need to execute an sql statement or a data step which lists out the records of another tables based on the company names.

Ex: select * from table1 where company_name in ('compa1','compa2');

This needs to be repeated for all the values of the initial data set.

Any help will be much appreciated.

Thanks

[Tom]

Why would you want to do it one row at a time?  Seems inefficient to me.

Why not just pull out all of the observations in one query?

If you need to keep them separate keep the values from the first dataset (or even better give each criteria its own unique id).

data have ;

  length a b $10 ;

  input a b ;

  row+1;

cards;

compa1     compb1

compa1     compb2

compa1     compb3

compa2     compb1

compa2     compb2

run;

proc sql noprint ;

  create table want as

   select a.*

        , b.*

   from have a

      , table1 b

   where b.company_name = a.a

      or b.company_name = a.b

  ;

quit;

[shyamili]

Hi Tom

Thanks for the prompt reply.

I tried doing that but couldn't give me the required answer.

Let me put my query in more detail.

My initial data set has variables something like compName, date, value.

The next dataset is ABC as mentioned above.

I need to group those companies by date, where company name is each row in dataset ABC, and find the average of values per each date.

[shyamili]

Something like

select avg(values) from

(select values from companies where compId in (123,124)) group by date;

Instead of those values, I need to put in the values of  each row of dataset ABC

[Tom]

It is still very confusing as to what your actual requirement is.

So if you have TABLE1 with variables COMPANY, DATE, VALUE and table LIST with variables COMPANY1, COMPANY2 then it sounds like you want to so something like

select a.company, a.date, mean(value) as mean_value

from TABLE1 A

where a.company in

   (select company1 as company from list

     union

     select company2 as company from list)

group by a.company, a.date

[shyamili]

Sorry! I understand that the problem statement is very confusing.

That dint work too

1. First I have the list of companies with various dates and stock values for that date.

2. I find the various combinations possible. Lets say n=2, I find all the possible combinations of two companies together.

3. Then for each of these possible combination (Ex: compA+compB), I find the average stock value based on the date. Thats the reason I used group by date.

4. Finally, after finding the averages for all dates of those two companies., I need to find the std dev of the averages.

5. This process needs to be repeated for all the possible combinations.

Now, I have all the possible combinations of companies in a table

I need to find the average stock value for every combination based on the date.

That's what I am looking at.

Thanks yet again!

[Tom]

I do not get how the two companies get involved in the analysis.

To find the average stock price for each company on each date you could use PROC SUMMARY.

proc summary data=have nway ;

  class company date ;

  var value ;

  output out=want mean=mean_value std=std_value ;

run;

[Tom]

Do you want some how add the prices of the two companies together?

proc sql ;

create table want as

select a.company1

        , a.company2

        , b.date

        , sum(b.value,c.value) as total

from list a

     , table1 b

     , table1 c

where a.company1 = b.company

    and a.company2 = c.company

    and b.date = c.date

;

[shyamili]

Thanks yet again!

Does this give me the sum of values of all company combinations mentioned in list?

That sounds great!

[Tom]

The idea here is that you are joining the list back to the master table twice so that you can find the value for each of the two companies.

I am not sure how you want to do your averaging.

1) Do you need to average the values before using them?  If you have just one value per company per day then there is no need to pre average the numbers. 

2) Do you want to average over all of the dates for the pair of companies? 

proc sql ;

create table want as

select x.company1

        , x.company2

        , mean(total) as average_total

from

(select a.company1

        , a.company2

        , b.date

        , sum(b.value,c.value) as total

from list a

     , table1 b

     , table1 c

where a.company1 = b.company

    and a.company2 = c.company

    and b.date = c.date

)

group by 1,2

;

[shyamili]

Amazing!! That worked!

But the problem is, it is good for two companies or maybe 3,4.

What if I need to combine n number of companies?