Creating an indicator variable for a group based on a particular value of a variable

[Zerg]

Hi all,

I'd like to create a indicator variable for the following data set:

ID   Year   Var1  Indicator

A    1990      .           1

A    1991      1          1

A    1992      2          1

B    1990      .           0

B    1991      0          0

B    1992      1          0

I want to set the indicator equals to 1 as long as any observation on Var1 within a particular group ID shows a value of 2, as shown in the sample data.

How can I program it in SAS? I appreciate any help.

Han

[Reeza]

SQL is likely your best bet here:

proc sql;
create table want as
select *, case when max(var1)=2 then 1 else 0 as indicator
from have
group by ID
order by 1, 2;
quit;

[Zerg]

Thank you for the prompt rely.

I followed your codes using the variables and data set I have:

proc sql;
create table c7 as
select *, case when max(row2yrs)=2 then 1 else 0 as rowtag
from c6
group by execid
order by gvkey, year;
quit;

It prompted error: 

822  proc sql;
823  create table c6 as
824  select *, case when max(row2yrs)=2 then 1 else 0 as rowtag
                                                      --
                                                      73
ERROR 73-322: Expecting an END.

825  from c6
826  group by execid
827  order by gvkey, year;
828  quit;

How can I  resolve it? Many thanks!

[Reeza]

Well....I forgot the END but the log told you that. Add in the word END before the word AS.

ERROR 73-322: Expecting an END

[Zerg]

Thank you for helping me out!

[novinosrin]

@Zerg While I completely agree with @Reeza with sql option assuming 2 is your max within a group ID, you can avoid case when for the reason you are anyways remerging. This helps binary operations

data have;
infile datalines truncover;
input ID $  Year   Var1;  
datalines;
A    1990      .           1
A    1991      1          1
A    1992      2          1
B    1990      .           0
B    1991      0          0
B    1992      1          0
;
proc sql;
create table want as
select *,  max(var1)=2 as indicator
from have
group by ID
order by 1, 2;
quit;

[novinosrin · Accepted solution]

And if you have higher values than 2 as in the below modified example, binary operation is still very simple-->

data have;
infile datalines truncover;
input ID $  Year   Var1;  
datalines;
A    1990      .           1
A    1991      1          1
A    1992      2          1
A	 1993      3	.	   
B    1990      .           0
B    1991      0          0
B    1992      1          0
;
proc sql;
create table want as
select *,  max(var1=2) as indicator
from have
group by ID
order by 1, 2;
quit;

[Zerg]

Thank you novinosrin. Your solution works perfectly! 🙂

[art297]

Like your log showed, the end statement was missing. However, is 2 the maximum value? If not, you could use something like:

data have (drop=indicator);
  input ID $  Year   Var1  Indicator;
  cards;
A    1990      .           1
A    1991      1          1
A    1992      2          1
B    1990      .           0
B    1991      0          0
B    1992      1          0
;

proc sql;
  create table want (drop=twovar) as
    select a.*,max(b.twovar) as twovar,
           case
             when not missing(twovar) then 1
             else 0 
           end as indicator
      from have a left join have (rename=(Var1=twovar) drop=year where=(twovar eq 2)) b
        on a.ID eq b.ID
          group by a.ID
            order by 1, 2
  ;
quit;

Art, CEO, AnalystFinder.com

[Ksharp]

data have (drop=indicator);
  input ID $  Year   Var1  Indicator;
  cards;
A    1990      .           1
A    1991      1          1
A    1992      2          1
B    1990      .           0
B    1991      0          0
B    1992      1          0
;

data want;
 merge have have(keep=id var1 rename=(var1=_var1) where=(_var1=2) in=inb);
 by id;
 flag=inb;
 drop _var1;
run;
proc print noobs;run;