topic Re: Creating an indicator variable for a group based on a particular value of a variable in SAS Programming

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

Zerg — Tue, 27 Mar 2018 21:11:48 GMT

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

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

Reeza — Tue, 27 Mar 2018 21:14:57 GMT

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;

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

Zerg — Tue, 27 Mar 2018 21:28:47 GMT

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!

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

novinosrin — Tue, 27 Mar 2018 21:35:55 GMT

@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;

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

novinosrin — Tue, 27 Mar 2018 21:41:51 GMT

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;

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

Reeza — Tue, 27 Mar 2018 21:42:35 GMT

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

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

art297 — Tue, 27 Mar 2018 21:43:02 GMT

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

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

Zerg — Tue, 27 Mar 2018 21:45:32 GMT

Thank you novinosrin. Your solution works perfectly! 🙂

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

Zerg — Tue, 27 Mar 2018 21:47:18 GMT

Thank you for helping me out!

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

Ksharp — Wed, 28 Mar 2018 12:34:57 GMT

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;