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
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;
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.