DATA Step, Macro, Functions and more

SAS data step and macros

Reply
Occasional Contributor
Posts: 6

SAS data step and macros

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

Super User
Super User
Posts: 6,500

Re: SAS data step and macros

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;

Occasional Contributor
Posts: 6

Re: SAS data step and macros

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.

Occasional Contributor
Posts: 6

Re: SAS data step and macros

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

Super User
Super User
Posts: 6,500

Re: SAS data step and macros

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

Occasional Contributor
Posts: 6

Re: SAS data step and macros

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!

Super User
Super User
Posts: 6,500

Re: SAS data step and macros

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;

Super User
Super User
Posts: 6,500

Re: SAS data step and macros

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

;

Occasional Contributor
Posts: 6

Re: SAS data step and macros

Thanks yet again!

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

That sounds great!

Super User
Super User
Posts: 6,500

Re: SAS data step and macros

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

;

Occasional Contributor
Posts: 6

Re: SAS data step and macros

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?

Ask a Question
Discussion stats
  • 10 replies
  • 336 views
  • 6 likes
  • 2 in conversation