BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

    3 REPLIES 3
    data_null__
    Jade | Level 19

    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;


    brophymj
    Quartz | Level 8

    Thank you

    Ksharp
    Super User
    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

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

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