Conditional Nested Loops

[EthanW1]

Say I have a table of two columns of Dates and VINs, and I want to implement conditional nested loops to create a counter. The condition would essentially count the number of observations of unique VINs within a series of unique dates.

DATES    VINS

 1880      4V6HJ

 1880      4V6HJ

 1880      6C8KI

 1881      4V6HJ

 1884      6C8KI

For example, the above table would give a counter value of 4 because there are 2 distinct VINs within a distinct date and 2 distinct VINs in separate unique dates. I know how to generally implement this code in other languages, but I'm unaware of how to do this in SAS program within an EG project given varying indexing syntax and condition.

I suppose I would generally write it like this; it would be nice to see this generalized into SAS program.

i=1; n=0;

for 1:(length of column)-1

      if date(i) = date(i+1)

           if vin(i) ~= vin(i+1)

                  n = n+1;

            end

      else if date(i) ~= date(i+1)

            n=n+1;

      end

i = i+1;

end

[Quentin · Accepted solution]

Assuming you're new to SAS, one key point is that the SAS DATA step is a loop.  So you don't have to write a loop to loop through records in a dataset. A simple data step reads a one record from a dataset, processes the record, outputs it and then loops to read the next record.

Sharing data as code makes it easier for people to help you.  You have data:

data have ;
  input dates vins $5.;
  cards ;
 1880 4V6HJ
 1880 4V6HJ
 1880 6C8KI
 1881 4V6HJ
 1884 6C8KI
;
run ;

You can create a counter with code like:

data want ;
  set have ;
  by dates vins ;
  if first.vins then counter++1 ;
  put (dates vins first.dates first.vins counter)(=) ;
run ;

Which will return:

20   data want ;
21     set have ;
22     by dates vins ;
23     if first.vins then counter++1 ;
24     put (dates vins first.dates first.vins counter)(=) ;
25   run ;

dates=1880 vins=4V6HJ FIRST.dates=1 FIRST.vins=1 counter=1
dates=1880 vins=4V6HJ FIRST.dates=0 FIRST.vins=0 counter=1
dates=1880 vins=6C8KI FIRST.dates=0 FIRST.vins=1 counter=2
dates=1881 vins=4V6HJ FIRST.dates=1 FIRST.vins=1 counter=3
dates=1884 vins=6C8KI FIRST.dates=1 FIRST.vins=1 counter=4
NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 5 observations and 3 variables

That simple data step makes use of a few novel SAS constructs, which are needed because the data step processes records one record at a time.

The first is BY-group processing.  The statement BY Dates Vins; tells SAS to create temporary boolean variables first.dates and first.vins which will be set to 1 (true) when the value  changes.  

The statement counter++1; is an example of using the SUM statement to create an accumulator.