BookmarkSubscribeRSS Feed
shyamili
Calcite | Level 5

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

10 REPLIES 10
Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Super User Tom
Super User

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
Calcite | Level 5

Thanks yet again!

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

That sounds great!

Tom
Super User Tom
Super User

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
Calcite | Level 5

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1264 views
  • 6 likes
  • 2 in conversation