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

hi,

 

I have 2 table as shown below

 

A table
Category Volume Minus_2000 Plus_2000
A 5000 3000 7000
B 25000 23000 27000
C 6000 4000 8000
D 9000 7000 11000
E 3000 1000

5000

 

B Table
Category Volume
P 4501
Q 3663
R 2888
S 8900
T 20000
V 25000
U 26000
D 9000

 

In "A Table" we have Minus_20 and Plus_20 as ranges for lookup. within that range if "B Table" Volume falls then we need to have category of "B Table" with "A Table" as below.

 

Required Table
Category Volume Minus_2000 Plus_2000 B_Category
A 5000 3000 7000 P
A 5000 3000 7000 Q
B 25000 23000 27000 V
C 6000 4000 8000 P
D 9000 7000 11000 S
E 3000 1000 5000 P
E 3000 1000 5000 Q
E 3000 1000 5000 R

 

Thanks,

Ganesh K

1 ACCEPTED SOLUTION

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

Post test data in the form of a datastep. 

 

What happens if minus to plus covers more than one value in the lookup?

 

Something like:

proc sql;
  create table WANT as
  select A.*,
         B.CATEGORY
  from   TABLEA A
  left join TABLEB B
  on     A.MINUS_2000 <= B.VOLUME < A.PLUS_2000;
quit;

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in the form of a datastep. 

 

What happens if minus to plus covers more than one value in the lookup?

 

Something like:

proc sql;
  create table WANT as
  select A.*,
         B.CATEGORY
  from   TABLEA A
  left join TABLEB B
  on     A.MINUS_2000 <= B.VOLUME < A.PLUS_2000;
quit;
Astounding
PROC Star

I think the SQL programmers will knock this one out quickly.  But first a couple of questions to clarify ...

 

"B" is matching with "V".  Shouldn't it also match with "U"?

 

Should the program ignore "D" matching with "D"?

ballardw
Super User

It is very good idea to post your data in the form of a data step. Otherwise we have to create such and may reach solutions that don't quite work because we do not know if some of your variables may be numeric or character.

 

And assuming that your output for Category B should B_category U and V and D should have S and D this might get you started:

data tableA;
   input Category $ Volume Minus_2000 Plus_2000 ;
datalines;
A 5000 3000 7000 
B 25000 23000 27000 
C 6000 4000 8000 
D 9000 7000 11000 
E 3000 1000 5000
;

data TableB;
   input Category $ Volume ;
datalines;
P 4501 
Q 3663 
R 2888 
S 8900 
T 20000 
V 25000 
U 26000 
D 9000 
;
run;

proc sql;
   create table want as
   select TableA.*, TableB.Category as B_Category
   from TableA,TableB
   where tableb.Volume between TableA.Minus_2000 and TableA.Plus_2000
   order by TableA.Category;
quit;

Note the data step code to create data and posted in the forum code box opened with {i} icon.

 

However if you took a separate step to create the Plus and minus take a look at:

proc sql;
   create table want2 as
   select TableA.*, TableB.Category as B_Category
   from TableA,TableB
   where  abs(TableA.Volume - Tableb.Volume) le 2000
   order by TableA.Category;
quit;

which may be more flexible in the long run as you don't need the Plus or Minus variables if the range is symetric (+ or - the same value)

 

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
  • 3 replies
  • 654 views
  • 1 like
  • 4 in conversation