SAS Support Communities

Dear Ballardw, thank you very much for your kind and insigthful replay. Maybe I should have posted the problem at an earlier stage as you suggested. let me try again. the dataset have is the result of this operation: data have1; input group $ match x; cards; a 1000 1 a 1000 2 a 1000 3 b 2000 1 b 2000 2 b 2000 3 b 2000 4 ; run; data have2; input group $ match id; cards; a 1000 2 a 1000 3 a 1000 4 b 2000 2 b 2000 3 b 2000 4 b 2000 5 ; run; proc sql; create table have as select a.*,b.id from have1 as a left join have2 as b on a.group=b.group and a.match=b.match order by group, id, x; quit; data have; retain group id x; set have; run;   In other terms I have to match the subject x of have1 within group with the id of have2. the two dataset have only in common the variable group and the variable match. the problem is that  within a group different subjects can have the same match variable but in the want dataset I need that x in each group be unique.   You are right the real problem is more complex as there can be exceeding rows in both datasete and/or the matching variable do not match. In reality what I would like is to have those that have not joined in residuals dataset. The problem presented is a simplification to start to solve the problem of having multiple subject with the same matching variable and an equal number of subjects.  Thank you again in advance.   ... View more

data have; input group $ id x; cards; a 2 1 a 2 2 a 2 3 a 3 1 a 3 2 a 3 3 a 4 1 a 4 2 a 4 3 b 2 1 b 2 2 b 2 3 b 2 4 b 3 1 b 3 2 b 3 3 b 3 4 b 4 1 b 4 2 b 4 3 b 4 4 b 5 1 b 5 2 b 5 3 b 5 4 ; run; ... View more

What it is needed is just that within group x is unique, even if it is choosen ar random within id ... View more

@RobPratt  Thank you very much Rob! I have a question, maybe just trivial. the objective function seems to be defined over all groups and not within each group. Isn't the solution different from the within group minimization? Unfortunately I don'have Sas Viya and cannot perform "by group processing".   Another question. my real data is much bigger: a larger number of groups (say about 10k), a huge number of subjects within (some of the groups- up to 1m) and, say, about 8k areas in few groups. do you foresee problems with the resources and have any suggestion to optimize the code? ... View more

Thank you Rick for your message. the reason why the freq does not return the same number of records for each area is that each group has different areas, or in other terms the fact the two groups has some areas in common is just a coincidence. rerunning the freq as follows all areas has the same number of observations, within each group. proc freq data=prova.have; tables group*area /list missing; run; The problem I posted is a minimization problem within group I will try to follow you suggestion, but since I am short of time I would appreciate very much any code.     ... View more

Reposted in SAS/OR forum ... View more

I apologize, I posted in the wrong forum. can I readdress it or have to post it again? ... View more

Hi, in the dataset have, for each group, there is the distance between each subject and many areas. in the dataset have_contraints, for each group, there is the number of subjects that belongs to each area. I would like to assign each subject to only one area minimizing the sum of distances within each group given the constraint that the number of subjects in each area within the group is that of table have_contraints. I would appreciate any solution with proc optmodel and/or with IML. As I am not proficient in any of them I would also appreciate very much a description of the code. thank you very much in advance ... View more

Thank you Ksharp. Very interesting solution. I learnt a new possibility. I'd like to wait a little more for an odslist solution.  ... View more

dear Paige, thank you for your answer. I had read that example and others, but my problem is a bit different, I think as the sublists and level of  indentation have to be initiated according to some conditions. ... View more

Thank You Koen, I marked my second message as solution by mistake, since I wanted to mark yours as solution. apologies.  I don't know how to undo the marking. I wrote to sas to ask the unmarking. Let's see if they respond. ... View more

thank you very much. the only thing missing is that I need to know which  is mached to any id, but I can obtain that with a little coding like this. is it correct?   proc sort data=sashelp.class out=class; by sex name; run; proc distance data=class out=dist method=euclid SHAPE=SQUARE; by sex; copy name; var interval(age height weight); run; data dist; set dist; by sex; retain id2; if first.sex then id2=1; else id2+1; run; data dist_trp(drop=dist1-dist10); *data dist_trp; set dist; by sex; array dist{10} dist1-dist10; do Person_withinSameSex = 1 to dim(dist); distance_euclid=dist(Person_withinSameSex); output; end; run; proc sql; create table dist_trp as select a.*,b.name as name_matched from dist_trp as a left join dist as b on a.sex=b.sex and a.Person_withinSameSex=b.id2 order by sex,id2,Person_withinSameSex; quit; ... View more