topic selecting data from a table (daily prices) conditional on data from another table (annual report) in SAS Data Management

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

ilikesas — Fri, 23 Jan 2015 02:19:21 GMT

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!

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

Haikuo — Fri, 23 Jan 2015 03:04:20 GMT

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;

;

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

ilikesas — Fri, 23 Jan 2015 03:23:02 GMT

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!

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

Haikuo — Fri, 23 Jan 2015 04:11:05 GMT

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

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

ilikesas — Fri, 23 Jan 2015 04:16:24 GMT

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

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

Haikuo — Fri, 23 Jan 2015 04:20:03 GMT

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

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

ilikesas — Fri, 23 Jan 2015 04:32:02 GMT

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

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

Ksharp — Fri, 23 Jan 2015 06:31:33 GMT

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

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

LinusH — Fri, 23 Jan 2015 13:27:20 GMT

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.

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

Haikuo — Fri, 23 Jan 2015 16:01:53 GMT

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