DATA Step, Macro, Functions and more

Data manipulation question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Data manipulation question

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!


Accepted Solutions
Solution
‎03-31-2017 02:14 PM
Super User
Super User
Posts: 7,039

Re: Data manipulation question

[ Edited ]

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


All Replies
Super User
Posts: 11,343

Re: Data manipulation question

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.

Occasional Contributor
Posts: 11

Re: Data manipulation question

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.

Super User
Super User
Posts: 7,039

Re: Data manipulation question

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;
Occasional Contributor
Posts: 11

Re: Data manipulation question

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!

Attachment
Attachment
Super User
Super User
Posts: 7,039

Re: Data manipulation question

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.

Solution
‎03-31-2017 02:14 PM
Super User
Super User
Posts: 7,039

Re: Data manipulation question

[ Edited ]

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;

 

Occasional Contributor
Posts: 11

Re: Data manipulation question

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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