Assistance needed to input a static observation if it does not exist already in the BY GROUP

[gtchambers3]

Hello -

I am trying to condition and manipulate some data that is focused on locality and then company name.  An example is below.

In the example below, the business "Olaf's Boat Cleaning Supplies" is not observed for Tampa.  I want to have SAS auto-create an observation for this company when this instance occurs.  I need this company to appear in each City.  I'm tapping my soon to be wrinkling forehead trying to figure this out.  Your help is appreciated!

country - state - county - city - business_name - var5 - var6 - var7 (and so on)

USA - Florida - Miami_Dade - Miami - Olaf s Boat Cleaning Supplies - -

USA - Florida - Miami_Dade - Miami - Mercury Boat Repair and Maintenance - -

USA - Florida - Citrus - Orlando - Olaf's Boat Cleaning Supplies - -

USA - Florida - Citrus - Orlando - Mercury Boat Repair and Maintenance - -

USA - Florida - Hillsborough - Tampa - Mercury Boat Repair and Maintenance - -

USA - Florida - Pinellas - Clearwater - Olaf's Boat Cleaning Supplies

USA - Florida - Pinellas - Clearwater - Mercury Boat Repair and Maintenance

[Tom]

Generally you can do this by making a skeleton or template dataset that has all of the possible combinations.

Do you have a list of cities?  or do you just want to use the list that appear in the data?

Do you have a list of businesses? or do you just want to use the list that appear in the data?

proc sql ;
create table skeleton as
select * 
from (select distinct country,state,county,city from have) A
   ,  (select distinct business_name from have) B
order by country,state,county,city,business_name
;
quit;

data want;
  merge skeleton have;
  by country state county city business_name;
run;

[gtchambers3]

Thank you for your reply!   A few quick questions -

in this example, if I create a dummy or skeleton list (which I have a pre-determined list that I can identify and use), when I merge, will I create two observations for cities where the business name exists - e.g., two Olaf's or Mercury in Miami in the new data set?

[Tom]

Should work. Try it.

Make sure that the skeleton file has only one observation per group.

And that the values are unique and coded the same. 

So if there are two different Olaf's in Miami you need to have some variable to tells them apart and include that in the skeleton dataset. You might have Olaf's 1 and Olaf' 2 as the business name.

Make sure the value match.  OLAF is not the same as Olaf.