I have a data as follows:
Class 1 and 2 have single row, that means these two class taught independently by A and B respectively.
Class 3 and 3 have multiple rows, that means these two class co-taught independently by several teachers. for example, class 3 was co-taught by teachers A, C, and D.
class 4 was co-taught by teachers B, C, and D.
I want to creat two variables "coteach" "N_teacher" and make the data as follows:
the variable "coteach" is an indicator variable, and "N_teacher" counts number of teahcers teach the class
Please supply your SAS data in data steps with datalines, so we know with what the code has to work.
Pictures(!) of spreadsheets tell us next to nothing about your SAS datasets, and can't be used for testing.
IT IS AN EXCEL FILE.
Then you should ask your question in an Excel forum 😉
Seriously, it is very hard to give advice if one cannot see the data as it is present in SAS. Especially with such a layout, where some texts may or may not be repeated over several observations.
what if i import the excel into SAS and proc print the data as follows:
1 | M5002 | A |
2 | M5032 | B |
3 | M5082 | A |
. | C | |
. | D | |
4 | M5082 | B |
. | C | |
. | D |
The first step needed is to expand the part-missing columns:
data have_expanded;
set have;
retain _class _class_code;
if class ne .
then _class = class;
else class = _class;
if class_code ne ""
then _class_code = class_code;
else class_code = _class_code;
drop _:;
run;
Then, this SQL should do it:
proc sql;
create table want as
select
class,
class_code,
teacher,
count(*) as n_teacher,
(calculated n_teacher > 1) as coteach
from have_expanded
group by class, class_code
;
quit;
And here the whole thing, along with creating the sample data:
data have;
infile datalines dlm=" " dsd;
input class $ class_code $ teacher $;
datalines;
1 M5002 A
2 M5032 B
3 M5082 A
C
D
4 M5082 B
C
D
;
data have_expanded;
set have;
retain _class _class_code;
if class ne .
then _class = class;
else class = _class;
if class_code ne ""
then _class_code = class_code;
else class_code = _class_code;
drop _:;
run;
proc sql;
create table want as
select
class,
class_code,
teacher,
count(*) as n_teacher,
(calculated n_teacher > 1) as coteach
from have_expanded
group by class, class_code
;
quit;
proc print data=have noobs;
run;
proc print data=want noobs;
run;
Results:
class_ class code teacher 1 M5002 A 2 M5032 B 3 M5082 A C D 4 M5082 B C D class_ class code teacher n_teacher coteach 1 M5002 A 1 0 2 M5032 B 1 0 3 M5082 A 3 1 3 M5082 D 3 1 3 M5082 C 3 1 4 M5082 C 3 1 4 M5082 B 3 1 4 M5082 D 3 1
PS I have shown you in this post how such a data step looks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: