topic Re: How to flag observations with multiple rows v.s. sing row? in SAS Programming

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

tinghlin — Sat, 10 Oct 2020 04:10:19 GMT

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

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

Kurt_Bremser — Sat, 10 Oct 2020 06:36:55 GMT

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.

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

Kurt_Bremser — Sat, 10 Oct 2020 06:39:39 GMT

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

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

tinghlin — Sat, 10 Oct 2020 08:37:30 GMT

IT IS AN EXCEL FILE.

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

Kurt_Bremser — Sat, 10 Oct 2020 10:34:15 GMT

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.

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

tinghlin — Sat, 10 Oct 2020 10:55:34 GMT

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

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

Kurt_Bremser — Sat, 10 Oct 2020 11:14:23 GMT

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;

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

Kurt_Bremser — Tue, 13 Oct 2020 08:05:12 GMT

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