BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
costasRO
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Super User
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
Fluorite | Level 6
Many thanks, works very well.
Quentin
Super User

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1565 views
  • 1 like
  • 4 in conversation