topic proc sql join with case when in SAS Programming

proc sql join with case when

sanjay1 — Fri, 21 Jul 2017 09:53:21 GMT

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.

Re: proc sql join with case when

RW9 — Fri, 21 Jul 2017 10:02:55 GMT

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;

Re: proc sql join with case when

LinusH — Fri, 21 Jul 2017 21:05:40 GMT

To be able verify that suggested solution meets your requirements, please provide an expected output data set.