Create a new variable to flag combinations of two other variables

[costasRO]

Hello All

I have a dataset that looks like this

Fund    Company              date           

   1           A                     1/1/1990          

   1           B                    1/1/1990          

   1           C                    1/1/1990         

  2            A                     1/1/1990         

  2            B                    1/1/1990          

 2            D                     1/1/1990         

I want to create a variable to indicate combinations of company and date like below:

Fund    Company              date           Indicator

   1           A                     1/1/1990          1

   1           B                    1/1/1990          2

   1           C                    1/1/1990         3

  2            A                     1/1/1990         1

  2            B                    1/1/1990          2

 2            D                     1/1/1990         4

I have many funds, companies and dates in the dataset.

many thanks for your help.

            Costas

[ballardw]

You do not actually describe any RULE for creating the flag. As far as I can tell all you are doing is applying a number based solely on the value of Company and I have no idea what the result might be if you have any company with two or more dates or a company other than A, B, C or D.

Programs require stated rules. Examples are helpful to demonstrate the rule, especially when more complex, but without a rule an example can be next to useless, especially when none of your company or dates differ within a group.

Part of your Rule(s) that should be stated is apparently what ever that indicator is should reset for each fund.

[Ksharp · Accepted solution]

data have;
input Fund    Company   $           date   :mmddyy10.;
format date mmddyys10.;
cards;
   1           A                     1/1/1990          
   1           B                    1/1/1990          
   1           C                    1/1/1990         
  2            A                     1/1/1990         
  2            B                    1/1/1990          
 2            D                     1/1/1990     
;
data want;
 if _n_=1 then do;
   Indicator=0;
   if 0 then set have;
   declare hash h();
   h.definekey('Company','date');
   h.definedata('Indicator');
   h.definedone();
 end;
set have;
if h.find() ne 0 then do;n+1;Indicator=n;h.add();end;
drop n;
run;

[costasRO]

Many thanks, works very well.

[Quentin]

Not quite sure about the logic you want, But if you want Indicator to identify a unique Company-Date within Fund, then I think below might be what you want.  It uses the sum statement to create an accumulator variable that is set to 0 for each fund, and then increments each time a new value for Company or Date is encountered.  The BY-group processing requires the data to be sorted. I added more test data.

data have;
input Fund Company $ date :mmddyy10.;
format date mmddyys10.;
cards;
1 A 1/1/1990
1 A 1/1/1990
1 A 2/1/1990
1 B 1/1/1990
1 C 1/1/1990
2 A 1/1/1990
2 B 1/1/1990
2 D 1/1/1990
;

data want ;
  set have ;
  by fund company date ;
  if first.fund then Indicator=0 ;
  if first.date then Indicator++1 ;

  put Fund Company Date Indicator ;
run ;

Returns:

46   data want ;
47     set have ;
48     by fund company date ;
49     if first.fund then Indicator=0 ;
50     if first.date then Indicator++1 ;
51
52     put Fund Company Date Indicator ;
53   run ;

1 A 01/01/1990 1
1 A 01/01/1990 1
1 A 02/01/1990 2
1 B 01/01/1990 3
1 C 01/01/1990 4
2 A 01/01/1990 1
2 B 01/01/1990 2
2 D 01/01/1990 3
NOTE: There were 8 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 8 observations and 4 variables.