topic Re: Transposing dataset with lots of fields in SAS Procedures

Transposing dataset with lots of fields

brophymj — Wed, 20 Aug 2014 09:40:32 GMT

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...	Field90	Car_Nos	Van_Nos	Truck_Nos	Bike_Nos	Bus_Nos	Car_Cover_Type	Van_Cover_Type	Truck_Cover_Type	Bike_Cover_Type	Bus_Cover_Ype
11232	...	...	2	12	33	2	1	C	A	A	B	B
12342	...	...	0	2	4	6	7	A	D	A	B	A

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.	Field1	Field90	Vehicle Type	Vehicle No's	Cover Type
11232	...	...	Car	2	C
11232	...	...	Van	12	A
11232	...	...	Truck	33	A
11232	...	...	Bike	2	B
11232	...	...	Bus	1	B
12342	...	...	Car	0	A
12342	...	...	Van	2	D
12342	...	...	Truck	4	A
12342	...	...	Bike	6	B
12342	...	...	Bus	7	A

Message was edited by: Matthew Brophy

Re: Transposing dataset with lots of fields

data_null__ — Wed, 20 Aug 2014 11:15:43 GMT

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; 

Re: Transposing dataset with lots of fields

brophymj — Wed, 20 Aug 2014 13:53:57 GMT

Thank you

Re: Transposing dataset with lots of fields

Ksharp — Wed, 20 Aug 2014 14:29:11 GMT

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