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

[ilikesas]

Hi,

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

report date	company	condition1	condition2
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

And daily prices table:

date	company	price
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

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	company	price
26/4/2010	A	12
27/10/2010	A	11
1/1/2011	C	25

Thank you!

[Haikuo · Accepted solution]

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;

;

[ilikesas]

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!

[Haikuo]

'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'.

[ilikesas]

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

[Haikuo]

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

[ilikesas]

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

[LinusH]

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.

[Haikuo]

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

[Ksharp]

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