Разделить данные по нескольким столбцам на строки (в SQL или SAS)

У меня есть данные в следующем виде:

Country    City1    City2    City3       AverageTemperature
UK         London   Glasgow  Manchester  15
Italy      Rome     Naples   Venice      25
Germany    Munich   Berlin               20
USA        New York                      25

С шагом данных SQL или SAS я хотел бы получить данные в этой форме:

Country    City        AverageTemperature
UK         London      15
UK         Glasgow     15
UK         Manchester  15
Italy      Rome        25
Italy      Naples      25
Italy      Venice      25
Germany    Munich      20
Germany    Berlin      20
USA        New York    25

Так что у меня есть данные по отдельным строкам. Я думал о том, чтобы сделать это, перебирая три столбца города, где город не является пустым, но я не уверен, как это сделать уверенно - легко ли это сделать с помощью SQL или SAS? Просто указатель будет принят с благодарностью.


sql sas proc-sql
person dplanet    schedule 22.12.2013    source источник

Ответы (4)


arrow_upward
2
arrow_downward 

SELECT COUNTRY, City1,  AverageTemperature FROM Table_Name
UNION ALL 
SELECT COUNTRY, City2,  AverageTemperature FROM Table_Name
UNION ALL 
SELECT COUNTRY, City3,  AverageTemperature FROM Table_Name

Чтобы получить строки, в которых столбец City не равен нулю, вы можете сделать что-то вроде этого

SELECT COUNTRY, City1,  AverageTemperature FROM Table_Name 
WHERE City1 IS NOT NULL
UNION ALL 
SELECT COUNTRY, City2,  AverageTemperature FROM Table_Name 
WHERE City2 IS NOT NULL
UNION ALL 
SELECT COUNTRY, City3,  AverageTemperature FROM Table_Name 
WHERE City3 IS NOT NULL
person M.Ali    schedule 22.12.2013

arrow_upward
2
arrow_downward

Простой шаг данных SAS

data out;
set in;
array cities[3] city1-city3;
format city $12.;
do i=1 to 3;
  if compress(cities[i]) ^= "" then do;
     city = cities[i];
     output;
  end;
end;
keep country city  AverageTemperature;
run;
person DomPazz    schedule 22.12.2013

arrow_upward
1
arrow_downward

Вы можете развернуть это, используя «относительно» стандартный SQL. Вот подход, который требует только однократного сканирования данных:

select country, city, averagetemperatur
from (select t.country,
             (case when n = 1 then t.city1
                   when n = 2 then t.city2
                   when n = 3 then t.city3
             end) as city,
            t.averagetemperature
     from t cross join
          (select 1 as n union all select 2 union all select 3) n
    ) t
 where city is not null;

Точный синтаксис для создания таблицы с тремя строками (n) может различаться в зависимости от базы данных.

person Gordon Linoff    schedule 22.12.2013

arrow_upward
0
arrow_downward

Цикл макроса должен выполнять эту работу:

%MACRO Cities ;
  %DO N=1 %TO 3 ;
    proc sql ;
      create table Cities_&N as
      select Country, City&N as City, AverageTemperature
      from your_table_name_here
      where City&N is not null ;
    quit ;
  %END ;

  data Cities ;
    set Cities_: ;
  run ;
%MEND ;

%Cities ;
person DTS    schedule 22.08.2014