Транспонирование строк в столбцы в SAS или SQL

Я новый пользователь SAS / SQL, и у меня есть набор данных, в котором мне нужно перенести некоторые строки в столбцы. Я думаю, что есть более быстрый или простой способ сделать это, и я хотел бы дать вам всем совет. Мой пример лучше объяснит мою проблему:

Вот набор данных, который у меня есть:

Month   ID     Car      Claim_Type   Cost_of_claim
  1    1243   Ferrari    Collision      12,000
  2    6437   Peugeot    Fire           50,000
  5    0184   Citroen    Stole           3,000
  9    1930   Fiat       Medical         1,000
  3    2934   GM         Liability      20,000

И мне нужно создать такой набор данных:

Month   ID     Car    Collision   Fire    Stole   Medical Liability
1    1243   Ferrari    12,000       0       0       0         0 
2    6437   Peugeot       0      50,000     0       0         0         
5    0184   Citroen       0         0      3,000    0         0
9    1930   Fiat          0         0       0     1,000       0
3    2934   GM            0         0       0       0      20,000

Я просто переставил несколько строк в столбцы ...

Я думал сделать что-то подобное, чтобы создать свой новый набор данных:

proc sql;
select Month, ID, CAR
  case when Claim_Type = 'Collision' then Cost_of_claim end Collision,
  case when Claim_Type = 'Fire'      then Cost_of_claim end Fire,
  case when Claim_Type = 'Stole'     then Cost_of_claim end Stole,
  case when Claim_Type = 'Medical'   then Cost_of_claim end Medical,
  case when Claim_Type = 'Liability' then Cost_of_claim end Liability
from my_table;

Проблема в том, что у вас огромное количество данных, и я думаю, что этот способ может быть не слишком эффективным. Кроме того, в моем наборе данных гораздо больше столбцов и строк, и я не хочу вводить все возможности в операторах case when, поскольку это не кажется простым (или удобным) для поддержки кода.

Может ли кто-нибудь помочь мне решить эту проблему?


sql transpose sas
person Rods2292    schedule 18.05.2016    source источник

Ответы (3)


arrow_upward
4
arrow_downward

PROC TRANSPOSE должен делать то, что вы хотите.

data test;
  input Month   ID     Car $     Claim_Type : $12. Cost_of_claim;
  cards;
  1    1243   Ferrari    Collision      12000
  2    6437   Peugeot    Fire           50000
  5    0184   Citroen    Stole           3000
  9    1930   Fiat       Medical         1000
  3    2934   GM         Liability      20000
run;

proc transpose data=test out=transposed;
  by notsorted month notsorted id notsorted car;
  var cost_of_claim;
  id claim_type;
run;

В выходном наборе данных нет недиагональных нулей, но вы можете добавить их на шаге данных, если они вам действительно нужны.

person dj_paige    schedule 18.05.2016
comment
NOTSORTED применяется ко всем оператору BY, если он не похож на параметр DESCENDING. Я обычно ставлю это в конце. - person data _null_; 18.05.2016

arrow_upward
0
arrow_downward

Вы можете попробовать динамический sql и pivot, но производительность будет зависеть от того, сколько у вас разных типов утверждений.

create table #mytable (Month int, ID int, Car varchar(20), Claim_Type varchar(20),  Cost_of_claim int)

insert into #mytable values 
(1, 1243, 'Ferrari', 'Collision', 12000)
, (2, 6437, 'Peugeot', 'Fire', 50000)
, (5, 184, 'Citroen', 'Stole', 3000)
, (9, 1930, 'Fiat', 'Medical', 1000)
, (3, 2934, 'GM', 'Liability', 20000)
, (12, 4455, 'Ford', 'Theft', 20)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Claim_Type) 
                    from #mytable
                    group by Claim_Type
                    order by Claim_Type
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + 'month,id,car,' + @cols + N' from 
             (
                select month,id, car, Cost_of_claim, Claim_Type
                from #mytable               
            ) x
            pivot 
            (
                max(Cost_of_claim)
                for Claim_Type in (' + @cols + N')
            ) p 
            '

exec sp_executesql @query;

drop table #mytable
person artm    schedule 18.05.2016

arrow_upward
0
arrow_downward

Этот метод заполняет макропеременную всеми возможными типами Claim_types и перебирает их, генерируя переменные так же, как и в вашем примере кода, поэтому вам не нужно вводить все возможные варианты. Переменная "backstop" используется из-за запятой в цикле (SAS выдаст ошибку без еще одной переменной после последней запятой на шаге proc sql).

data have;
   input Month ID Car $12. Claim_Type $12. Cost_of_claim;
   datalines;
  1    1243   Ferrari    Collision      12000
  2    6437   Peugeot    Fire           50000
  5    0184   Citroen    Stole           3000
  9    1930   Fiat       Medical         1000
  3    2934   GM         Liability      20000
    ;
run;


%macro your_macro;

    proc sql noprint;
        select distinct claim_type into: list_of_claims separated by " " from have;

        create table want (drop = backstop) as select
            month, id, car,
                %do i = 1 %to %sysfunc(countw(&list_of_claims.));
                %let this_claim = %scan(&list_of_claims., &i.);
                    case when claim_type = "&this_claim." then cost_of_claim else 0 end as &this_claim.,
                %end;
            1 as backstop
        from have;
    quit;

%mend your_macro;

%your_macro;
person Sean    schedule 18.05.2016