proc sql join with case when

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 114
Accepted Solution

proc sql join with case when

Hi SAS Experts,

 

I am tryng to join two  tables based on cntry and num, 

and if a.value(from test1) > b.value(from test2) then i want to bring the "prct" from
test2 table ;

 


data test1;
input cntry$ name$ value num;
datalines;
india abc 10 1
india ccf 11 2
us aba 22 3
china abca 20 4
;
run;

 

data test2;
input cntry$ value num prct;
datalines;
india 20 1 0.1
india 9 2 0.4
us 22 3 0.5
china 22 4 0.6
china 23 3 0.8
;
run;

 

 

Here is my code and I think  this is not right. Please help


Proc sql;
create table sample as
select a.Cntry,a.name,a.num,a.value from
test1 as a
case (when a.Cntry=b.cntry and a.num=b.num
and a.Value > b.value then prct end) as percentile1
left join test2 as b on a.Cntry=b.Cntry;
quit;

 

Thanks & Regards,

Sanjay.

 


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 7,392

Re: proc sql join with case when

Yes, your putting select items in the join area.  Thats not how it should work.  Try (as I am guessing as you haven;t provided a required output):

proc sql;
  create table SAMPLE as
  select  A.CNTRY,
          A.NAME,
          A.NUM,
          A.VALUE,
          case when A.VALUE > B.VALUE then PRCT else . end as PERCENTILE1      
  from    TEST1 A
  left join TEST2 B 
  on      A.CNTRY=B.CNTRY
  and     A.NUM=B.NUM;
quit;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 7,392

Re: proc sql join with case when

Yes, your putting select items in the join area.  Thats not how it should work.  Try (as I am guessing as you haven;t provided a required output):

proc sql;
  create table SAMPLE as
  select  A.CNTRY,
          A.NAME,
          A.NUM,
          A.VALUE,
          case when A.VALUE > B.VALUE then PRCT else . end as PERCENTILE1      
  from    TEST1 A
  left join TEST2 B 
  on      A.CNTRY=B.CNTRY
  and     A.NUM=B.NUM;
quit;
Super User
Posts: 5,254

Re: proc sql join with case when

To be able verify that suggested solution meets your requirements, please provide an expected output data set.
Data never sleeps
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 140 views
  • 0 likes
  • 3 in conversation