BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

9 REPLIES 9
Haikuo
Onyx | Level 15

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
Barite | Level 11

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
Onyx | Level 15

'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
Barite | Level 11

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
Onyx | Level 15

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

ilikesas
Barite | Level 11

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

LinusH
Tourmaline | Level 20

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
Haikuo
Onyx | Level 15

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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