Help using Base SAS procedures

combine rows where one row has null value

Reply
Occasional Contributor
Posts: 7

combine rows where one row has null value

 

how can I get these on the same row to look like this

 

Region  Vehicle  year1  color1   year2  color 2

South   impala    2014    blue     2015     black

 

 

Region  Vehicle  year1  color1   year2  color 2

South   impala    2014   blue

South   impala                              2015     black

 

Super User
Posts: 5,257

Re: combine rows where one row has null value

Depends on how the rest of your data set look like.
How did you get this layout? It looks like a transpose that went wrong.
I would prefer a normalized table design, which is more flexible to most reporting requirements. That would mean to transpose columns to rows, having one year, and one colour column. For your particular example it would be a quite simple use of the coalesce () function.
Data never sleeps
Super User
Posts: 5,085

Re: combine rows where one row has null value

Here's an approach that doesn't require you to spell out all the variables you need to work with:

 

proc sort data=have;

by region vehicle;

run;

 

data want;

update have (obs=0) have;

by region vehicle;

run;

 

Good luck.

Ask a Question
Discussion stats
  • 2 replies
  • 250 views
  • 2 likes
  • 3 in conversation