I am having 2 data sets as A and B.
As B data set contains names of the cities and the capacity of records it can handel .
In Data set A also contains name of the cities, segment,product etc...
segments are as follows :
EL, L, M,H EH,
so the records from the data set A should fill the counter limit defined in Dada set B.
e.g city Pune=5000 then all records pretaining to pune should get allocated and should not exceed the limit 5000.
and the records should get allocated/ tagged segment wise and where segment has priority while allocation.
Segemnt Priority
EL 1
L 2
M 3
H 4
EH 5
1. so if there are 1000 records for segment EL where city =pune then it should get allocated and then it should get reduced from the limit i.e 5000-1000=4000
and the remaining counter should be filled with the remainng segments based on priority. The limit allocted should be tagged as X and remaning records exceeding
the counter should be Tagged as Y.
2. So if there are 6000 records of segment EL where city=Pune then 5000 should get allocated or flagged as X and the remaning 1000 should be flagged as Y
Data set B:
city Company X Limit
Pune 500
mumbai 700
delhi 1000
chennai 300
conditions:
1. the limit of the city will be changing every month.
2. the rows should not extend the limit and should get tagged as x else y
3. the tagging should be according to segment priority.
Data Set A:
AGREEMENTID Allocation BRANCH SCHME_DESC segment
1556791 INHOUSE MUMBAI HL-VERIFIED ELR
1074377 INHOUSE MUMBAI HL-VERIFIED ELR
1171072 INHOUSE MUMBAI HL-VERIFIED ELR
1338092 INHOUSE PUNE IFB102JUNE ELR
281705 INHOUSE JALANDHAR NO INCOME (**bleep**) ELR
284747 INHOUSE LUDHIANA NO INCOME PROOF (**bleep**) ELR
292577 INHOUSE NAGPUR GREEN CHANNEL ELR
256129 INHOUSE JAIPUR GREEN CHANNEL ELR
292598 INHOUSE CHANDIGARH NO INCOME (**bleep**) ELR
287598 INHOUSE JALANDHAR Income ELR
297420 INHOUSE NAGPUR NO INCOME ELR
211674 INHOUSE MUMBAI HFCHL- LR
226198 INHOUSE DELHI HL- LR
327194 INHOUSE MUMBAI HFCHL- LR
1300414 INHOUSE PUNE SONY1JUN LR
29261 INHOUSE PUNE Self LR
36345 INHOUSE BARODA Vacant LR
387062 INHOUSE INDORE SELF LR
387064 INHOUSE INDORE SELF LR
4819364 FR NASIK AGRI PROGRAM MR
359295 INHOUSE DELHI MULTIPLE USE MR
359337 INHOUSE INDORE MULTIPLE USE MR
332245 INHOUSE CHENNAI SELF MR
382035 INHOUSE JALANDHAR Self MR
523088 INHOUSE KOLKATA Rented MR
533513 INHOUSE DELHI HE GROSS MR
I am not 100% sure I follow your rules as I don't see any example input data sets or a an example result. The narrative sounds sort of like a linear programming exercise where your data set B has constraints.
The SAS/OR module has a number of procedures such as CLP or OPTNET that may be of use if you have access to the module.
Otherwise it may be best to provide small example data sets with the results. Hint: the constraints should be 3 or 4 records instead of thousands.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.