SAS flags

[serrld113]

Hi all,

I'm having a hard time with my flags. I could use your expertise. I have a set of students and classes. I want to flag certain classes these students have taken by giving each class a column. If the student has taken that class then 1, else 0. The problem I'm running into is that if the student has taken more than one of those classes, my flags aren't staying in the same row...but a new row is created and a flag added. I want all flags in one row, by the stu_id. I added a sample code:  

****example;
data have; 
input stu_id $ class $;
datalines;
00614GHK M1223
00614GHK M4567
0069KB0H M1223
008HC4RM M4567
008HC4RM S2023
008HC4RM S3024 
009WJHED M1223
; 
run;

proc sql;
create table want as
select distinct stu_id, 
		case when class = 'M1223' then 1 else 0 end as M1223, 
		case when class = 'M4567' then 1 else 0 end as M4567,
		case when class = 'S2023' then 1 else 0 end as S2023,
		case when class = 'S3024' then 1 else 0 end as S3024
from have
;
quit;

(Please feel free to format this part...I don't know how to do it myself...)

I want my table to look like this:

stu_id M1223 M4567 S2023 S3024

00614GHK 1 1 0 0
0069KB0H 1 0 0 0
008HC4RM 0 1 1 1
009WJHED 1 0 0 0

Thank you!

[Reeza]

Do you only have three or four codes? If you do, your solution won't scale, you'll have to type out all your codes.

There are several ways to create dummy variables listed here:

https://blogs.sas.com/content/iml/2016/02/22/create-dummy-variables-in-sas.html

Otherwise, you can probably modify your code like:

proc sql;
create table want as
select distinct stu_id, 
		max(case when class = 'M1223' then 1 else 0 end, 0) as M1223, 
		max(case when class = 'M4567' then 1 else 0 end, 0) as M4567,
		max(case when class = 'S2023' then 1 else 0 end, 0) as S2023,
		max(case when class = 'S3024' then 1 else 0 end, 0) as S3024
from have
group by stu_id
;
quit;

---

@serrld113 wrote:

Hi all,

I'm having a hard time with my flags. I could use your expertise. I have a set of students and classes. I want to flag certain classes these students have taken by giving each class a column. If the student has taken that class then 1, else 0. The problem I'm running into is that if the student has taken more than one of those classes, my flags aren't staying in the same row...but a new row is created and a flag added. I want all flags in one row, by the stu_id. I added a sample code:  

****example;
data have; 
input stu_id $ class $;
datalines;
00614GHK M1223
00614GHK M4567
0069KB0H M1223
008HC4RM M4567
008HC4RM S2023
008HC4RM S3024 
009WJHED M1223
; 
run;

proc sql;
create table want as
select distinct stu_id, 
		case when class = 'M1223' then 1 else 0 end as M1223, 
		case when class = 'M4567' then 1 else 0 end as M4567,
		case when class = 'S2023' then 1 else 0 end as S2023,
		case when class = 'S3024' then 1 else 0 end as S3024
from have
;
quit;

(Feel free to format this part...I don't know how to do it myself...)

I want my table to look like this:

stu_id M1223 M4567 S2023 S3024

00614GHK 1 1 0 0
0069KB0H 1 0 0 0
008HC4RM 0 1 1 1
009WJHED 1 0 0 0

Thank you!

---

[Reeza · Accepted solution]

That SQL was wrong, but a data step is cleaner.

data have; 
input stu_id $ class $;
datalines;
00614GHK M1223
00614GHK M4567
0069KB0H M1223
008HC4RM M4567
008HC4RM S2023
008HC4RM S3024 
009WJHED M1223
; 
run;


*Sort;
proc sort data=have;
by stu_id;
run;

data want;
set have;
by stu_id;

*keep values across rows;
retain M1223 M4567 S2023 S3024 0;

*set to 0 at first of each ID;
if first.stu_id then do;
M1223=0; M4567=0; S2023=0; S3024=0;
end;

*create dummy variables;
if class = 'M1223' then M1223=1; 
else if class = 'M4567' then M4567=1;
else if class = 'S2023' then S2023=1;
else if class = 'S3024' then S3024=1;

*output if last ID;
if last.stu_id then output;

run;

[Ksharp]

data have; 
input stu_id $ class $;
value=1;
datalines;
00614GHK M1223
00614GHK M4567
0069KB0H M1223
008HC4RM M4567
008HC4RM S2023
008HC4RM S3024 
009WJHED M1223
; 
run;
proc sort data=have out=temp;
by stu_id  class ;
run;
proc transpose data=temp out=temp1(drop=_:);
by stu_id;
id class;
var value;
run;
proc stdize data=temp1 out=want reponly missing=0;
var _numeric_;
run;