Блог

Главная - Вопросы по программированию - Каков наиболее эффективный способ создания новых идентификаторов групп из перекрестно классифицированных вложенных групп в SAS?

Каков наиболее эффективный способ создания новых идентификаторов групп из перекрестно классифицированных вложенных групп в SAS?

#sas #sas-macro #proc-sql

#sas #sas-макрос #proc-sql

Вопрос:

У меня есть две таблицы. Первый содержит около 370 000 строк с данными из программ колледжа за период с 2010 по 2019 год. Соответствующие столбцы: year , program_id , college_id . Я сохранил program_name и college_name просто для облегчения понимания. Вот как выглядят мои данные:

 | year | college_id | college_name         | program_id | program_name           |
|------|------------|----------------------|------------|------------------------|
| 2010 | 001        | Wilbor College       | 001        | Civil Engineering      |
| 2010 | 001        | Wilbor College       | 002        | Electrical Engineering |
| 2010 | 001        | Wilbor College       | 003        | Mechanical Engineering |
| 2010 | 001        | Wilbor College       | 021        | English                |
| 2010 | 002        | Mary College         | 031        | Physics                |
| 2010 | 003        | Francis College      | 041        | Arts                   |
| 2019 | 001        | Wilbor College       | 004        | Engineering            |
| 2019 | 101        | South Wilbor College | 022        | English Teaching       |
| 2019 | 101        | South Wilbor College | 023        | English and Spanish    |
| 2019 | 101        | South Wilbor College | 024        | English Literature     |
| 2019 | 223        | Maryamp;Francis College | 031        | Physics                |
| 2019 | 223        | Maryamp;Francis College | 032        | Astronomy              |
| 2019 | 223        | Maryamp;Francis College | 033        | Geophysics             |
| 2019 | 223        | Maryamp;Francis College | 034        | Biophysics             |
| 2019 | 223        | Maryamp;Francis College | 041        | Arts                   |
| 2019 | 223        | Maryamp;Francis College | 042        | Visual Arts            |
| 2019 | 223        | Maryamp;Francis College | 043        | History of Art         |
| 2019 | 223        | Maryamp;Francis College | 044        | Cinema                 |

Вторая таблица содержит метаданные, содержащие около 15 000 строк, с информацией о связанных программах, а также два столбца program_id и linked_program_id . Вот как это выглядит:

 
| program_id | linked_program_id |
|------------|-------------------|
| 001        | 002               |
| 004        | 001               |
| 004        | 002               |
| 004        | 003               |
| 021        | 022               |
| 021        | 023               |
| 023        | 021               |
| 023        | 024               |
| 031        | 032               |
| 032        | 031               |
| 033        | 031               |
| 034        | 031               |
| 041        | 042               |
| 041        | 043               |
| 042        | 044               |

Эта вторая таблица важна, поскольку она позволяет идентифицировать программы, которые связаны друг с другом. Существует несколько типов ссылок, но, проще говоря, ссылки — это случаи, когда значение program_id может меняться со временем. Обычно эти изменения происходят из-за того, что есть программы, которые разделяются или объединяются годами.

Это также происходит с колледжами (разделение и слияние), но у меня нет одинаковых метаданных для колледжей. Однако его можно создать, если я смогу отслеживать их программы на протяжении многих лет.

Я хочу добавить две переменные в первый набор данных:

  1. group_p_id : идентификатор, одинаковый для всех program_id значений, которые каким-то образом связаны на протяжении многих лет;
  2. group_c_id : идентификатор, который одинаков для всех college_id значений, которые каким-то образом связаны на протяжении многих лет.

Результирующий набор данных будет выглядеть примерно так:

 | year | col_id | college_name         | prog_id | program_name           | group_p_id | group_c_id |
|------|--------|----------------------|---------|------------------------|------------|------------|
| 2010 | 001    | Wilbor College       | 001     | Civil Engineering      | 004        | 001        |
| 2010 | 001    | Wilbor College       | 002     | Electrical Engineering | 004        | 001        |
| 2010 | 001    | Wilbor College       | 003     | Mechanical Engineering | 004        | 001        |
| 2010 | 001    | Wilbor College       | 021     | English                | 021        | 001        |
| 2010 | 002    | Mary College         | 031     | Physics                | 031        | 223        |
| 2010 | 003    | Francis College      | 041     | Arts                   | 041        | 223        |
| 2019 | 001    | Wilbor College       | 005     | Engineering            | 004        | 001        |
| 2019 | 101    | South Wilbor College | 022     | English Teaching       | 021        | 001        |
| 2019 | 101    | South Wilbor College | 023     | English and Spanish    | 021        | 001        |
| 2019 | 101    | South Wilbor College | 024     | English Literature     | 021        | 001        |
| 2019 | 223    | Maryamp;Francis College | 031     | Physics                | 031        | 223        |
| 2019 | 223    | Maryamp;Francis College | 032     | Astronomy              | 031        | 223        |
| 2019 | 223    | Maryamp;Francis College | 033     | Geophysics             | 031        | 223        |
| 2019 | 223    | Maryamp;Francis College | 034     | Biophysics             | 031        | 223        |
| 2019 | 223    | Maryamp;Francis College | 041     | Arts                   | 041        | 223        |
| 2019 | 223    | Maryamp;Francis College | 042     | Visual Arts            | 041        | 223        |
| 2019 | 223    | Maryamp;Francis College | 043     | History of Art         | 041        | 223        |
| 2019 | 223    | Maryamp;Francis College | 044     | Cinema                 | 041        | 223        |

Here are the datasets in SAS input format (ids are in numeric format to make transformations easy):

 data have1;
INPUT year 1-4 college_id 6-8 college_name $10-29 program_id 31-33 program_name $35-56;
datalines;
2010 001 Wilbor College       001 Civil Engineering
2010 001 Wilbor College       002 Electrical Engineering
2010 001 Wilbor College       003 Mechanical Engineering
2010 001 Wilbor College       021 English
2010 002 Mary College         031 Physics
2010 003 Francis College      041 Arts
2019 001 Wilbor College       004 Engineering
2019 101 South Wilbor College 022 English Teaching
2019 101 South Wilbor College 023 English and_Spanish
2019 101 South Wilbor College 024 English Literature
2019 223 Maryamp;Francis College 031 Physics
2019 223 Maryamp;Francis College 032 Astronomy
2019 223 Maryamp;Francis_College 033 Geophysics
2019 223 Maryamp;Francis College 034 Biophysics
2019 223 Maryamp;Francis College 041 Arts
2019 223 Maryamp;Francis College 042 Visual Arts
2019 223 Maryamp;Francis College 043 History of Art
2019 223 Maryamp;Francis College 044 Cinema
;
run;

data have2;
INPUT program_id 1-3 linked_program_id 5-7;
datalines;
001 002
004 001
004 002
004 003
021 022
021 023
023 021
023 024
031 032
032 031
033 031
034 031
041 042
041 043
042 044
;
run;

data want;
INPUT year 1-4 college_id 6-8 college_name $10-29 program_id 31-33 program_name $35-56 broad_c_id 58-60 broad_c_id 62-64;
datalines;
2010 001 Wilbor College       001 Civil Engineering      004 001
2010 001 Wilbor College       002 Electrical Engineering 004 001
2010 001 Wilbor College       003 Mechanical Engineering 004 001
2010 001 Wilbor College       021 English                021 001
2010 002 Mary College         031 Physics                031 223
2010 003 Francis College      041 Arts                   041 223
2019 001 Wilbor College       005 Engineering            004 001
2019 101 South Wilbor College 022 English Teaching       021 001
2019 101 South Wilbor College 023 English and Spanish    021 001
2019 101 South Wilbor College 024 English Literature     021 001
2019 223 Maryamp;Francis College 031 Physics                031 223
2019 223 Maryamp;Francis College 032 Astronomy              031 223
2019 223 Maryamp;Francis College 033 Geophysics             031 223
2019 223 Maryamp;Francis College 034 Biophysics             031 223
2019 223 Maryamp;Francis College 041 Arts                   041 223
2019 223 Maryamp;Francis College 042 Visual Arts            041 223
2019 223 Maryamp;Francis College 043 History of Art         041 223
2019 223 Maryamp;Francis College 044 Cinema                 041 223
;
run;

The tricky thing is that the second table (the metadata) is not very well organized. Some ids appear in both in columns program_id and linked_program_id in a «many-to-many» way.

I tried to solve this problem myself, but the code is getting very complicated, and I am not so sure anymore if I am doing the right thing. So here is what I did:

  1. Make sure that every id is listed in both columns (both as program_id and linked_program_id ):
 proc sql;
create table temp_1 as
select distinct
program_id,
linked_program_id
from have2
union
select distinct
linked_program_id as program_id,
program_id as linked_program_id
from have2;
quit;
  1. Compute the maximum of program_id by linked_program_id, vice-versa, and then the maximum of each row:
 proc sql;
create table temp_2 as 
select
max(ID_0, ID_1) as ID_2,
*
from (select 
   max(linked_program_id) as ID_1, 
   *                 
   from (select 
        max(program_id) as ID_0, 
        *
        from temp_1
        group by linked_program_id) 
    group by program_id);
quit;
  1. На последнем шаге один и тот же новый идентификатор присваивался большинству связанных программ, но не всем из них. Поэтому я повторил ту же логику последнего шага 3 раза в макросе (используя новый идентификатор id_2 в качестве первого ввода):

(Для примера набора данных, который я предоставил здесь, было достаточно запустить цикл один раз. Но в моем наборе данных из 15 000 строк мне пришлось запускать его 3 раза, пока я не получил одинаковый идентификатор для всех связанных программ. )

 %macro max_rep;
proc sql;
%do i=3 %to 9 %by 3; 
create table temp_%eval(amp;i. 2) as 
select
max(ID_amp;i., ID_%eval(amp;i. 1)) as ID_%eval(amp;i. 2),
program_id, linked_program_id
from (select 
    max(ID_amp;i.) as ID_%eval(amp;i. 1),
    *
    from (select 
        max(ID_%eval(amp;i.-1)) as ID_amp;i.,
        *
        from temp_%eval(amp;i.-1)
        group by linked_program_id) 
    group by program_id);

drop table temp_%eval(amp;i.-1);

%end;
quit;
%mend;
%max_rep;
  1. Наконец, я выполняю соединение между have1 и temp_11 :
 proc sql;
create table want as
select
t1.*,
t2.ID_11 as group_p_id
from have1 as t1 
left join (select distinct ID_11, program_id from temp_11) as t2
on t1.program_id=t2.program_id;
quit;

Это дало мне половину того, что я хотел. Теперь у меня есть уникальный идентификатор для каждой программы, принадлежащей к одной и той же группе связанных программ. Я внес некоторые дополнительные изменения в зависимости от других переменных, чтобы получить более широкую программу каждой связанной группы в качестве моей group_p_id (вместо только максимального значения). Затем я понял, что мне все еще нужно выяснить, как создать новый идентификатор группы для колледжей ( group_c_id ). Должен быть более простой и эффективный способ сделать это — SAS, не так ли?

Любая помощь приветствуется.

Комментарии:

1. Это очень похоже по своей природе на пример кода для «Как группировать людей по имени ИЛИ фамилии» в devenezia.com/downloads/sas/samples/#hash8 . Общее предложение «Дано: P = p{i} = (p{i,1),p{i,2}), набор пар (ключ1, ключ2). Найдите: отдельные группы, G = g{x}, из P, такие, что каждая пара p в группе g обладает следующим свойством: ключ1 соответствует ключу1 любой другой пары в g. -или- ключ2 соответствует ключу2 любой другой пары в g.»

2. Большое вам спасибо, @Richard, я собираюсь взглянуть на это!