topic Re: (Proc SQL or Data step) with macros in SAS Programming

(Proc SQL or Data step) with macros

Punxsutawney — Thu, 13 Aug 2015 13:17:47 GMT

I have 2 sets of data with no common variable. One is a list of library members while the other is a list of libraries. Now, for each member, I want to assign the member to a library (and only one library) based on conditions like library capacity, calculated distance between the library and member, etc. The library capacity should be updated (as in reduced by 1) for the particular library after each assignment of a member to it. I am currently stuck at this and will appreciate any help or ideas. Thanks.

Re: (Proc SQL or Data step) with macros

Quentin — Thu, 13 Aug 2015 15:00:18 GMT

Hi, I suggest you show some small sample datasets. When you say library I'm thinking you mean SAS library. But then when I ready more I'm thinking you mean a library building that stores books and the members are people not datasets.... Sample data would help. Ideally both sample input data and the desired output. Also sounds like you may need to think about what you are trying to optimize (e.g. assign members to libraries, optimizing the average distance a member needs to travel to reach a library?). Or perhaps you don't really care about optimization, so it is sufficient to just consecutively assign each member to the best of the libraries that have capacity at that time.

Re: (Proc SQL or Data step) with macros

Punxsutawney — Thu, 13 Aug 2015 17:13:22 GMT

Hi,

Thanks for the reply. I meant library as a place that stores books, etc. Sorry about the confusion.

Member list

mbr_id zipcode

1 12134

2 12315

3 14235

Library list

lib_id zipcode capacity

15 12541 40

18 14521 45

21 13214 43

Don't worry about optimization. So what I want to do is assign a library ID to each of these members based on first capacity of the library (max capacity is 46) and distance.

I hope this helps. Thanks.

Re: (Proc SQL or Data step) with macros

Quentin — Thu, 13 Aug 2015 17:54:27 GMT

Are you a fan of hash tables? I could imagine a DATA step that reads through the Member list, and loads the library list into a hash table. So it reads in a member, rips through the hash table looking for the closest library that has capacity, makes the library assignment (dataset variable), decrements the capacity in the hash table by one. Then the DATA step iterates again and reads the second member etc. Sound reasonable?

It's in interesting exercise. Is it a homework exercise?