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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

7 REPLIES 7
ballardw
Super User

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.

shovakc
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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;
shovakc
Fluorite | Level 6

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!

Tom
Super User Tom
Super User

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.

Tom
Super User Tom
Super User

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;

 

shovakc
Fluorite | Level 6

Yes!! That is exactly what I am looking for. Thank you for your help. Have a nice weekend!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1718 views
  • 3 likes
  • 3 in conversation