Splitting into three tables with different if/then logic

[Buzzy_Bee]

Sorry if this seems a bit simple, but I'm trying to split the table into three new tables so that if the company has only contact (just the main contact, shown as 'Main' in the Contact field) then I'll put that record into a table main_only (so for example, Tools Ltd only has one contact so that goes into main_only). Then Cheese Ltd goes into the second table named both_match because both the Main and Secondary contact match on Location as they're both in Berlin. Thirdly, Walmarty has three contacts - the main contact and a secondary contact live in Chicago, but there is another secondary contact who lives in Sydney, so I want this company in the no_match table because not all of the contacts are based in the same location. Could someone please help me to get the code right and tell me whether I'm going about this in a sensible way before I try this out in SAS? Thanks so much for any ideas. The first contact listed is always the main contact.

CompanyID	CompanyName	Name	Contact	Location
123456	Walmarty	Joe Bloggs	Main	Chicago
123456	Walmarty	Will Smith	Secondary	Chicago
123456	Walmarty	Jane	Secondary	Sydney
9988	Tools Ltd	Bob	Main	London
77777	Cheese Ltd	George	Main	Berlin
77777	Cheese Ltd	Rahul	Secondary	Berlin

data main_only both_match no_match;

   set customer.addresses;

   if last.companyID and Contact='Main' then output main_only;

   else if first.companyID=last.companyID and last.Contact='Secondary' and first.Location=last.Location then output both_match;

   else if first.companyID=last.companyID and last.Contact='Secondary' and first.Location ne last.Location then output no_match;

run; 

[ChrisNZ]

This makes no sense:

if last.Contact='Secondary' 

last.CONTACT  is a Boolean.

It can never be equal to anything but 0 or 1.

[Kurt_Bremser · Accepted solution]

What you need is a double DO loop:

data have;
infile datalines dlm=',' dsd truncover;
input CompanyID $ CompanyName :$20. Name :$10. Contact :$10.  Location :$10.;
datalines;
123456,Walmarty,Joe Bloggs,Main,Chicago
123456,Walmarty,Will Smith,Secondary,Chicago
123456,Walmarty,Jane,Secondary,Sydney
9988,Tools Ltd,Bob,Main,London
77777,Cheese Ltd,George,Main,Berlin
77777,Cheese Ltd,Rahul,Secondary,Berlin
;

data
  main_only
  both_match
  no_match
  no_main
;
do until (last.companyid);
  set have;
  by companyid notsorted;
  if first.companyid then _loc = location;
  select (contact);
    when ('Main') _main = 1;
    when ('Secondary') _sec = 1;
  end;
  if location ne _loc then _mult = 1;
end;
do until (last.companyid);
  set have;
  by companyid notsorted;
  if _main and not _sec then output main_only;
  else if _main and _sec
  then do;
    if _mult then output no_match;
    else output both_match;
  end;
  else output no_main;
end;
drop _:;
run;

Note that I added a fourth output to catch eventual mistakes (no 'Main' contact).

[Buzzy_Bee]

Thank you so much for writing out this code - it works perfectly. 

I'd tried out the online version of the SAS advanced programming course, but I found it extremely light on the first/last variable examples. Your code will really help me to apply these concepts to customer data sets.