topic Re: Flag observations that fulfill two conditions together in SAS Programming

Flag observations that fulfill two conditions together

PDevi — Thu, 09 Aug 2018 12:38:45 GMT

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.

Re: Flag observations that fulfill two conditions together

PGStats — Thu, 09 Aug 2018 03:27:00 GMT

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)

Re: Flag observations that fulfill two conditions together

PGStats — Thu, 09 Aug 2018 04:32:54 GMT

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;

Re: Flag observations that fulfill two conditions together

PDevi — Thu, 09 Aug 2018 12:40:05 GMT

The code returns only blanks.

Re: Flag observations that fulfill two conditions together

PeterClemmensen — Thu, 09 Aug 2018 12:49:38 GMT

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;

Re: Flag observations that fulfill two conditions together

PDevi — Wed, 15 Aug 2018 20:00:39 GMT

@PeterClemmensen It worked, thanks.

Re: Flag observations that fulfill two conditions together

PDevi — Wed, 15 Aug 2018 20:23:21 GMT

@PGStatsIt worked, thank you.

Re: Flag observations that fulfill two conditions together

PGStats — Wed, 15 Aug 2018 20:05:07 GMT

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

Re: Flag observations that fulfill two conditions together

PDevi — Wed, 15 Aug 2018 20:24:08 GMT

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