How to flag observations with multiple rows v.s. sing row?

[tinghlin]

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 

[Kurt_Bremser]

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.

[tinghlin]

IT IS AN EXCEL FILE. 

[Kurt_Bremser]

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.

[tinghlin]

what if i import the excel into SAS and proc print the data as follows:

Obs class class_code teacher12345678

1	M5002	A
2	M5032	B
3	M5082	A
.		C
.		D
4	M5082	B
.		C
.		D

[Kurt_Bremser]

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;

[Kurt_Bremser]

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   

[Kurt_Bremser]

PS I have shown you in this post how such a data step looks.