Flag observations that fulfill two conditions together

[PDevi]

I am very new to SAS and would appreciate patience and help.

I have a data set containing student id, term and audit_type. Each term has 2 audit_types and the student can be present at either of them or be present at both. I need to create a flag for each of these 3 scenarios for each student id each term: 1) if the student is present at only audit_type_1, 2) if s/he is present at only audit_type_2 and 3) if s/he is present at audit_type_1 and audit_type_2 both during that term.

**Sample data**
---------------

*Id*            *Term*               *Audit_type*
1                 Fall 2015              1
1                 Fall 2015              2
2                 Winter 2016         1
3                 Winter 2016         2
4                 Spring 2016         1
4                 Spring 2016         2

I was able to create a flag for the first 2 scenarios using case when as seen below:

proc sql;

create table test as
select id, term, audit_type,
case
when audit_type in ('audit_type_1') then 1
when audit_type in ('audit_type_2 ') then 2
end as audit_type_flag
from have;

I can't figure out how to flag the third scenario. So, I want something like below:

*Id*            *Term*                  *Audit_type*           *Flag*   
1                 Fall 2015              1                             3
1                 Fall 2015              2                             3
2                 Winter 2016         1                             1
3                 Winter 2016         2                             2
4                 Spring 2016         1                             3
4                 Spring 2016         2                             3

All help will be highly appreciated. Thanks in advance for your help and support.

[PGStats]

You can get the flag value by summing the audit_type numbers :

proc sql;

create table test as
select 
    id, 
    term, 
    audit_type,
    sum(input(scan(audit_type, 3, "_"), 1.)) as audit_type_flag
from have
group by id;

quit;

(untested)

[PGStats · Accepted solution]

It gets simpler for the edited version of your question:

proc sql;

create table test as
select 
    id, 
    term, 
    audit_type,
    sum(distinct audit_type) as audit_type_flag
from have
group by id, term;

quit;

[PDevi]

The code returns only blanks.

[PeterClemmensen]

To be sure to get a usable code answer, include sample data in the form of a data step. @PGStatss solution is good. Run the code below. It generates your desired data set.

data have;
length Term $20;
infile datalines dlm=',';
input Id $ Term $ Audit_type;
datalines;
1,Fall 2015,1
1,Fall 2015,2
2,Winter 2016,1
3,Winter 2016,2
4,Spring 2016,1
4,Spring 2016,2
;

proc sql;
   create table want as
   select 
       id, 
       term, 
       audit_type,
       sum(distinct audit_type) as audit_type_flag
   from have
   group by id, term;
quit;

[PDevi]

@PeterClemmensen It worked, thanks.

[PDevi]

@PGStatsIt worked, thank you.

[PGStats]

Try it. Ask a new question if you get stuck.The Forum is there for you.

[PDevi]

I tested and it worked. 🙂 Thanks for your encouragement.