Help using Base SAS procedures

Transposing dataset with lots of fields

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

Transposing dataset with lots of fields

I have a dataset with about 100 fields, 10 of which relate to vehicle type  - 5 rows represent vehicle numbers for each type and the other five are vehicle cover for each type. I want to change the dataset so that the vehcile numbers and cover are transposed so for each row in the original dataset I will create 5 new rows and two new columns and the original 10 columns will no longer exist.

Do I have to specify every field I want to transpose over or is there any easy way of selecting all fields and just specifying the fields you want to transpose. Also, how to you label the two new columns. I know the default label is "Former variable names.."

Any advice on the coding would be great.

Many thanks

Here's a simple example of my dataset:

Policy No.Field1...Field90Car_NosVan_NosTruck_NosBike_NosBus_NosCar_Cover_TypeVan_Cover_TypeTruck_Cover_TypeBike_Cover_TypeBus_Cover_Ype
11232......2123321CAABB
12342......02467ADABA

Fields 1 to 90 containing lots of details about the policy. I want to transpose this data set so it looks like this...

Policy No.Field1Field90Vehicle TypeVehicle No'sCover Type
11232......Car2C
11232......Van12A
11232......Truck33A
11232......Bike2B
11232......Bus1B
12342......Car0A
12342......Van2D
12342......Truck4A
12342......

Bike

6B
12342......Bus7A

Message was edited by: Matthew Brophy


Accepted Solutions
Solution
‎08-20-2014 07:15 AM
Respected Advisor
Posts: 3,799

Re: Transposing dataset with lots of fields

I think for this data using a data step for the transpose will be a good approach.

data policy;
   input policy:$5. field1 $ Field90 $ (Car_Nos  Van_Nos  Truck_Nos   Bike_Nos Bus_Nos)(:4.) (Car_Cover_Type Van_Cover_Type Truck_Cover_Type  Bike_Cover_Type   Bus_Cover_tYpe)(:$1.);
   list;
  
cards;
11232 ...   ...   2  12 33 2  1  C  A  A  B  B
12342 ...   ...   0   2 4  6  7  A  D  A  B  A
;;;;
   run;
data tall;
   set policy;
   array nos
  • Car_nos--Bus_nos;
  •    array typ
  • Car_cover_type--Bus_cover_type;
  •    length vehicle $8 vnos 8 type $1;
      
    do i = 1 to dim(nos);
          vehicle = scan(vname(nos),1,'_');
          vnos = nos;
          type = typ;
         
    output;
         
    end;
      
    keep policy field: vehicle vnos type;
       run;
    proc print;
      
    run;


    View solution in original post


    All Replies
    Solution
    ‎08-20-2014 07:15 AM
    Respected Advisor
    Posts: 3,799

    Re: Transposing dataset with lots of fields

    I think for this data using a data step for the transpose will be a good approach.

    data policy;
       input policy:$5. field1 $ Field90 $ (Car_Nos  Van_Nos  Truck_Nos   Bike_Nos Bus_Nos)(:4.) (Car_Cover_Type Van_Cover_Type Truck_Cover_Type  Bike_Cover_Type   Bus_Cover_tYpe)(:$1.);
       list;
      
    cards;
    11232 ...   ...   2  12 33 2  1  C  A  A  B  B
    12342 ...   ...   0   2 4  6  7  A  D  A  B  A
    ;;;;
       run;
    data tall;
       set policy;
       array nos
  • Car_nos--Bus_nos;
  •    array typ
  • Car_cover_type--Bus_cover_type;
  •    length vehicle $8 vnos 8 type $1;
      
    do i = 1 to dim(nos);
          vehicle = scan(vname(nos),1,'_');
          vnos = nos;
          type = typ;
         
    output;
         
    end;
      
    keep policy field: vehicle vnos type;
       run;
    proc print;
      
    run;


    Super Contributor
    Posts: 259

    Re: Transposing dataset with lots of fields

    Posted in reply to data_null__

    Thank you

    Super User
    Posts: 10,023

    Re: Transposing dataset with lots of fields

    data policy;
       input policy:$5. field1 $ Field90 $ (Car_Nos  Van_Nos  Truck_Nos   Bike_Nos Bus_Nos)(:4.) (Car_Cover_Type Van_Cover_Type Truck_Cover_Type  Bike_Cover_Type   Bus_Cover_Type)(:$1.);
       list; 
       cards; 
    11232 ...   ...   2  12 33 2  1  C  A  A  B  B
    12342 ...   ...   0   2 4  6  7  A  D  A  B  A
    ;;;;
       run; 
    proc sql noprint;
    select name into : list1 separated by ' ' from dictionary.columns where libname='WORK' and memname='POLICY' and name like '%Nos';
    select name into : list2 separated by ' ' from dictionary.columns where libname='WORK' and memname='POLICY' and name like '%Cover_Type';
    quit;
    %put &list1;
    %put &list2;
    data want(drop=i &list1 &list2);
     set policy;
     array x{*} &list1 ;
     array y{*} $ &list2;
     do i=1 to dim(x);
      VehicleType=scan(vname(x{i}),1,'_');
      VehicleNos=x{i};
      CoverType=y{i};
      output;
     end;
    run;
    

    Xia Keshan

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 3 replies
    • 188 views
    • 0 likes
    • 3 in conversation