topic Re: Create a new variable to flag combinations of two other variables in SAS Programming

Create a new variable to flag combinations of two other variables

costasRO — Fri, 24 Feb 2023 10:05:16 GMT

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

Re: Create a new variable to flag combinations of two other variables

ballardw — Fri, 24 Feb 2023 11:44:52 GMT

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.

Re: Create a new variable to flag combinations of two other variables

Ksharp — Fri, 24 Feb 2023 12:36:17 GMT

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;

Re: Create a new variable to flag combinations of two other variables

Quentin — Fri, 24 Feb 2023 13:34:03 GMT

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.

Re: Create a new variable to flag combinations of two other variables

costasRO — Tue, 28 Feb 2023 09:26:32 GMT

Many thanks, works very well.