Hi SAS community members,
I have a data manipulation question. I have attached a spreadsheet with current data in "Sheet 1" and what I need in "Sheet 2". The data is at the occupant level. I want to change it to vehicle level. However, there is no vehicle ID. But the data is arranged in such a way that you could distinguish it by vehicle. For example, as you can see in the spreadsheet, the first obs is a driver (DRV), followed by front seat passenger (FSP), and then rear seat child passenger (RP1C). The fourth and fifth obs are both drivers only without any passengers and so on and so forth. I would like to arrange it in such a way that all occupants of vehicle 1 are in one row by creating additional variables as displayed in "Sheet 2" but I am having trouble doing so. There are six types of occupants: DRV, FSP, RP1C, RP2C, RP1P, and RP2P. If anyone has suggestions on how to achieve this, I would really appreciate it. Thank you for you help!
Are you asking how to define a new variable to use to group the records by vehicle? Is it as simple as a new vehicle starts when the occupant type is the driver?
Some thing like:
data new ;
set have ;
vehicle_id + (occupant_type='DRV');
run;
proc transpose data=new out=want ;
by vehicle_id ;
var occupant_type;
run;
Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
If you have a SAS data set you can provide that in the form of data step code. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Thank you Ballardw. I will provide a SAS version of the data. I looked at the data step code and realized it could take me a while to understand how to use the macro. In the meantime, I will provide SAS data file. Thanks for the suggestion.
Did you try just using PROC TRANSPOSE?
data have ;
input Site Vehicle_Type $ Occupant_Type $ @@;
cards;
1 OV DRV 1 OV FSP 1 OV RP1C 1 PT DRV 1 PT DRV 1 PT FSP
;
proc transpose data=have out=want prefix= Occupant_Type ;
by Site Vehicle_Type;
var Occupant_Type;
run;
Thank you Tom. However this does not work because there is no vehicle ID and some vehicles have drivers only whereas others have varied number of passengers. I have attached SAS data files to this reply. The "Example" file has the original data set whereas the "final" file has the type of data structure that I want. Thanks!
People don't want to download SAS datasets any more than they want to download other file types. Just modify my data step above to include an example of a set of records where the PROC TRANSPOSE doesn't work. That is what we mean about posting your data. Something we can just highlight , copy, paste and run.
If you have more complex rules than just turning N observations into N variables then you need to explain them in more detail.
Are you asking how to define a new variable to use to group the records by vehicle? Is it as simple as a new vehicle starts when the occupant type is the driver?
Some thing like:
data new ;
set have ;
vehicle_id + (occupant_type='DRV');
run;
proc transpose data=new out=want ;
by vehicle_id ;
var occupant_type;
run;
Yes!! That is exactly what I am looking for. Thank you for your help. Have a nice weekend!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.