turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SAS data step and macros

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-07-2013 02:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shyamili

11-07-2013 03:16 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-07-2013 03:58 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shyamili

11-07-2013 04:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shyamili

11-07-2013 05:02 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-07-2013 05:22 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shyamili

11-07-2013 06:11 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shyamili

11-07-2013 06:15 PM

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

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-07-2013 06:20 PM

Thanks yet again!

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

That sounds great!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shyamili

11-07-2013 06:25 PM

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

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-07-2013 06:30 PM

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?