Counting number of unique dates across multiple variables

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

Marc_Leslie
Posts: 5
Joined: Mon Feb 11, 2013 7:10 pm

Counting number of unique dates across multiple variables

Postby Marc_Leslie » Mon Feb 11, 2013 7:14 pm

I have a database where each line of data is one person. There are 8 columns that have dates in them and some of these dates are missing. I would like to create a new variable that tells me, for these 8 columns, how many unique dates per row. For example, columns 1-7 have the date 2/10/13 and column 8 has the date 2/11/13; the new variable would tell me that there are 2 unique dates.

Any advice would be appreciated.
mirirai
Posts: 6
Joined: Thu Feb 07, 2013 9:46 am

Re: Counting number of unique dates across multiple variable

Postby mirirai » Tue Feb 19, 2013 2:21 pm

Are the days sorted? I.e. the first variable holds the earliest date an the last one the last date?
Marc_Leslie
Posts: 5
Joined: Mon Feb 11, 2013 7:10 pm

Re: Counting number of unique dates across multiple variable

Postby Marc_Leslie » Tue Feb 19, 2013 4:28 pm

Not at all. Any of the 8 variables can be the first date, the last date, or missing.
mirirai
Posts: 6
Joined: Thu Feb 07, 2013 9:46 am

Re: Counting number of unique dates across multiple variable

Postby mirirai » Tue Feb 19, 2013 6:42 pm

This solution uses two nested Loop to compare each variable with all the others.
I hope this works for you.
You might need to adjust the data type for dates ?!

Code: Select all

* Getting some test data
DATA LIST /d1 TO d8 1-16.
BEGIN DATA
1 2 3 4 5 6 7 8
1 1 1 4 5 1 0 4
0 2 0 0 0 0 0 0            
1 4 6 1 4 0 1 0
1 2 1 0 3 5 0 6  
0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1
END DATA.

MISSING VALUES d1 TO d8 (0).

********************************
* Initiate Variable for unique date counting with maximum number.
COMPUTE date_count = 8.
EXECUTE.

* Repeat for every Variable.
* If variable value is Missing draw 1.
* Else compare this variable with following variables 
* until two variables have the same value (draw 1 and break inner loop).
VECTOR dvec = d1 TO d8.
LOOP #i=1 TO 8.
  DO IF MISSING(dvec(#i)).
      COMPUTE date_count = date_count - 1.
  ELSE IF #i=8.
      BREAK.
   ELSE.
      LOOP #j=#i+1 TO 8.
         DO IF dvec(#i) = dvec(#j).
             COMPUTE date_count = date_count - 1.
             BREAK.
         END IF.
      END LOOP.    
  END IF.
END LOOP.
EXECUTE.
Marc_Leslie
Posts: 5
Joined: Mon Feb 11, 2013 7:10 pm

Re: Counting number of unique dates across multiple variable

Postby Marc_Leslie » Tue Feb 19, 2013 8:51 pm

I don't understand what this syntax means at all, but it worked perfectly. Thanks! This is such a relief to have this done!

Who is online

Users browsing this forum: No registered users and 2 guests

cron