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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

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

 


Accepted Solutions
Solution
‎03-27-2018 05:44 PM
PROC Star
Posts: 1,796

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

Posted in reply to novinosrin

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;

View solution in original post


All Replies
Super User
Posts: 23,700

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

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;
Occasional Contributor
Posts: 9

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

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!

Super User
Posts: 23,700

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

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

 

 

Occasional Contributor
Posts: 9

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

Thank you for helping me out!
PROC Star
Posts: 1,796

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

[ Edited ]

@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;
Solution
‎03-27-2018 05:44 PM
PROC Star
Posts: 1,796

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

Posted in reply to novinosrin

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;
Occasional Contributor
Posts: 9

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

Posted in reply to novinosrin
Thank you novinosrin. Your solution works perfectly! Smiley Happy
PROC Star
Posts: 8,164

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

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

 

Super User
Posts: 10,778

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 193 views
  • 4 likes
  • 5 in conversation