turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How to obtain this output?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-01-2017 11:04 AM

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

Accepted Solutions

Solution

02-02-2017
04:03 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ganeshk

02-01-2017 11:11 AM

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;

All Replies

Solution

02-02-2017
04:03 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ganeshk

02-01-2017 11:11 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ganeshk

02-01-2017 11:14 AM

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"?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ganeshk

02-01-2017 11:21 AM

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)