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
I think for this data using a data step for the transpose will be a good approach.
I think for this data using a data step for the transpose will be a good approach.
Thank you
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.