Dummy coding in SAS

Question

Apologies for the uninformative title. This is actually a fairly simple problem which I could easily do myself in matlab or perhaps stata, but the professor demands it be in SAS, which seems to have been designed for robots not humans.

Say I have a matrix of farm output data with rows of crops at the farm and field level. For example, farm 1 has 3 fields so there are three rows for farm 1: $F_{11}, F_{12}, F_{13}$

A crop column indicates the crop grown on each field of each farm.

$F_{11}=corn$

$F_{12}=beans$

$F_{13}=squash$

(The crops would actually be represented by a numerical value.)

I need to come up with a SAS script that separates corn farms from non corn farms. I.e., I need to create a new binary cornfarm=0 or 1 variable. Corn farms are defined as any farm growing corn on any of its fields.

Corresponding pseudo-code would be something like this:

for i=1 to farmtotal
  for j=1 to farmfieldtotal
    if F(i,j)=corn then cornfield(i,j)=1
    next j
  if sum(cornfield(i))>0 then cornfarm(i)=1
  next i

Answer 1

Since this sounds like homework I'll provide some advice rather than a runnable solution.

I'm going to assume that your data has a FARM_ID variable that is unique by farm, and that you've sorted your data by FARM_ID. Then, you can add farm level indicators to your farm-crop level dataset in two steps:

1. Generate a list, unique by FARM_ID, of FARM_IDs that appear on at least one observation where the CROP is "corn".
2. Merge this farm level list against your original farm-crop level dataset by FARM_ID and set your corn crop indicator to 1 on every match.

For step 1, you can use by-group processing in a data step to examine the entire group of crop records for each farm and output only those FARM_IDs that have what you are looking for. By-group processing is described by Paul Dickman here.

For step 2, you can use a second data step with the in= option on a merge statement to create your indicators on your original farm-crop level dataset. See UCLA's tutorial on merging and look for the in= option about three quarters of the way down.

There may be simpler approaches depending on the size and structure of your data, but this one should generalize to problems matching the description you provided.