topic Re: SAS data step and macros in SAS Programming

SAS data step and macros

shyamili — Thu, 07 Nov 2013 19:29:17 GMT

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

Re: SAS data step and macros

Tom — Thu, 07 Nov 2013 20:16:14 GMT

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;

Re: SAS data step and macros

shyamili — Thu, 07 Nov 2013 20:58:16 GMT

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.

Re: SAS data step and macros

shyamili — Thu, 07 Nov 2013 21:01:15 GMT

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

Re: SAS data step and macros

Tom — Thu, 07 Nov 2013 22:02:13 GMT

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

Re: SAS data step and macros

shyamili — Thu, 07 Nov 2013 22:22:36 GMT

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!

Re: SAS data step and macros

Tom — Thu, 07 Nov 2013 23:11:22 GMT

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;

Re: SAS data step and macros

Tom — Thu, 07 Nov 2013 23:15:22 GMT

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

;

Re: SAS data step and macros

shyamili — Thu, 07 Nov 2013 23:20:01 GMT

Thanks yet again!

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

That sounds great!

Re: SAS data step and macros

Tom — Thu, 07 Nov 2013 23:25:58 GMT

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

;

Re: SAS data step and macros

shyamili — Thu, 07 Nov 2013 23:30:35 GMT

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?