SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

selecting data from a table (daily prices) conditional on data from another table (annual report)

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

selecting data from a table (daily prices) conditional on data from another table (annual report)

Hi,

suppose I have a table of end of year annual reports:

report datecompanycondition1condition2
25/12/2010A11
24/12/2012A10
21/12/2013B00
30/12/2009B01
26/12/2011C11
31/12/2014C00

And daily prices table:

date     companyprice
26/4/2010A12
1/1/2011A16
27/10/2010A11
21/3/2012C20
1/1/2011C25
1/1/2009C30

What I would like to get are the daily prices of the days in the years for which a company has a annual report, and for which both conditions = 1:

date    companyprice
26/4/2010A12
27/10/2010A11
1/1/2011C25

Thank you!


Accepted Solutions
Solution
‎01-22-2015 10:04 PM
Respected Advisor
Posts: 3,124

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

data report;

     input date:ddmmyy10. company$   condition1 condition2;

     cards;

25/12/2010 A 1 1

24/12/2012 A 1 0

21/12/2013 B 0 0

30/12/2009 B 0 1

26/12/2011 C 1 1

31/12/2014 C 0 0

;

data price;

     input date:ddmmyy10.       company$   price;

     format date ddmmyy10.;

     cards;

26/4/2010 A 12

1/1/2011 A 16

27/10/2010 A 11

21/3/2012 C 20

1/1/2011 C 25

1/1/2009 C 30

;

proc sql;

     create table want as

           select P.* from report r, price p

                where r.condition1=1 and r.condition2=1

                     and year(p.date)=year(r.date)

                     and r.company=p.company

     ;

quit;

;

View solution in original post


All Replies
Solution
‎01-22-2015 10:04 PM
Respected Advisor
Posts: 3,124

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

data report;

     input date:ddmmyy10. company$   condition1 condition2;

     cards;

25/12/2010 A 1 1

24/12/2012 A 1 0

21/12/2013 B 0 0

30/12/2009 B 0 1

26/12/2011 C 1 1

31/12/2014 C 0 0

;

data price;

     input date:ddmmyy10.       company$   price;

     format date ddmmyy10.;

     cards;

26/4/2010 A 12

1/1/2011 A 16

27/10/2010 A 11

21/3/2012 C 20

1/1/2011 C 25

1/1/2009 C 30

;

proc sql;

     create table want as

           select P.* from report r, price p

                where r.condition1=1 and r.condition2=1

                     and year(p.date)=year(r.date)

                     and r.company=p.company

     ;

quit;

;

Super Contributor
Posts: 413

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

Hi Hai,

thank you for the code, very straightforward and neat!

I was just wandering, what does the  "P. "  in  " select P.* from report r, price p "  mean?

Also, suppose that I wanted to do the same thing but this time select the prices of the next year based on this year's annual report,

could I do it like this:

and year(p.date)=year(r.date)+1  ?


Thank you!

Respected Advisor
Posts: 3,124

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

'p' or 'r' refers table 'price' and table 'report', it is a common usage in the Proc SQL.

The answer to your second question is 'Yes'.

Super Contributor
Posts: 413

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

I understand it when you name report r and price p, its just the P. in the beginning that seems new to me

Thank you

Respected Advisor
Posts: 3,124

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

SAS does not differentiate case here. so when select P.*, meaning every column in the table 'price'.

Super Contributor
Posts: 413

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

Ah ok I see now, and if I wanted to select everything from two tables I would do p. r. *?

Super User
Posts: 5,257

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

Since this is the Data Management forum, let me conclude that this problem could be done by using the Join, or a combination of Extract and Lookup transformations in DI Studio.

But it sounds like the OP is up for a reporting requirement, so in a BI Server environment, i would recommend an Information Map.

Or, if Visual Analytics is available, have this lookup done in the Data Builder.

Data never sleeps
Respected Advisor
Posts: 3,124

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

In this case, just * will do. You can certainly use p.*, r.* as well.

Super User
Posts: 9,682

Re: selecting data from a table (daily prices) conditional on data from another table (annual report)

Hash Table version.

data report;
     input date:ddmmyy10. company$   condition1 condition2;
     cards;
25/12/2010 A 1 1
24/12/2012 A 1 0
21/12/2013 B 0 0
30/12/2009 B 0 1
26/12/2011 C 1 1
31/12/2014 C 0 0
;
data price;
     input date:ddmmyy10.       company$   price;
     format date ddmmyy10.;
     cards;
26/4/2010 A 12
1/1/2011 A 16
27/10/2010 A 11
21/3/2012 C 20
1/1/2011 C 25
1/1/2009 C 30
;
run;
data key(keep=company year); 
 set report;
 year=year(date);
 if condition1 and condition2;
run;
data want;
if _n_ eq 1 then do;
 if 0 then set key;
 declare hash h(dataset:'key');
 h.definekey(all:'y');
 h.definedone();
end;
 set price;
 year=year(date);
 if h.check()=0;
run;

Xia Keshan

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 425 views
  • 3 likes
  • 4 in conversation