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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
LinusH
Tourmaline | Level 20
To be able verify that suggested solution meets your requirements, please provide an expected output data set.
Data never sleeps

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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