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!
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;
;
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;
;
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!
'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'.
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
SAS does not differentiate case here. so when select P.*, meaning every column in the table 'price'.
Ah ok I see now, and if I wanted to select everything from two tables I would do p. r. *?
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.
In this case, just * will do. You can certainly use p.*, r.* as well.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.