Solved
New Contributor
Posts: 4

# Combine observations with same name

Hi,

I am relatively new to SAS programming. I have a data set that looks like this:

xxx yyy 1 2 3 4 5 6

a    01   .  .  z  .  .  .

a    02   .  x  .  .  .  .

a    02   .  . .  .   c  .

b    01   v  .  .  .  .  .

b    01   .  b .  .  .  .

b    01   .  .  n .   .  .

c    01   .  .  .  m   .  .

And my question is how can I make the set to be with a single observation for the variable yyy and to look like this:

xxx yyy 1 2 3 4 5 6

a    01   .  .  z  .  .  .

a    02   . x   .  . c  .

b    01  v b  n  .  .  .

c    01  .  .  .  m  .  .

Accepted Solutions
Solution
‎06-05-2017 06:01 AM
Super User
Posts: 9,227

## Re: Combine observations with same name

Well, a slight modification to @data_null__'s solution here:

https://communities.sas.com/t5/Base-SAS-Programming/code-to-pull-forward-missing-values/m-p/345821#M...

Is this:

```data sales;
input Product \$ Brand \$ January February March April May June;
datalines;
TV SAMSUNG . . 1 . . .
TV SAMSUNG . . . 2 . .
TV SAMSUNG . . . . . 3
TV LG . . 2 . . .
TABLET IPAD . 1 . . . .
TABLET SAMSUNG . . . 5 . .
LAPTOP LENOVO 1 . . . . .
LAPTOP DELL . 2 . . . .
LAPTOP DELL . . 3 . . .
;
run;

proc sort data=sales;
by product brand;
run;

data locf;
update sales(obs=0) sales;
by product brand;
if last.brand then output;
run;
```

All Replies
Super User
Posts: 9,227

## Re: Combine observations with same name

Please post test data in the form of a datastep.

As such, not typing that out so this is just a guess (as your variable structure is not given and you are mixing character data - z x etc, with numeric missing value .  Also variables cannot be called 1 or 2, so I assume _1 or _2), I would retain the variables (but you could also do merge, and possibly even summing up the variables), also what happens when more than one appear?

```data want;
set have;
array orig{6} _1--_6;
array new{6} \$1;
by xxx yyy;
retain new:;
do i=1 to 6;
if orig{i} ne "" then new{i}=orig{i};
end;
if last.yy then output;
run;```
New Contributor
Posts: 4

## Re: Combine observations with same name

So basically what I have is a data set like this:

data sales;
input Product \$ Brand \$ January February March April May June;
datalines;
TV SAMSUNG . . 1 . . .
TV SAMSUNG . . . 2 . .
TV SAMSUNG . . . . . 3
TV LG . . 2 . . .
TABLET IPAD . 1 . . . .
TABLET SAMSUNG . . . 5 . .
LAPTOP LENOVO 1 . . . . .
LAPTOP DELL . 2 . . . .
LAPTOP DELL . . 3 . . .
;

And what I am looking for is a data set that looks like this:

data sales2;
input Product \$ Brand \$ January February March April May June;
datalines;
TV SAMSUNG . . 1 2 . 3
TV LG . . 2 . . .
TABLET IPAD . 1 . . . .
TABLET SAMSUNG . . . 5 . .
LAPTOP LENOVO 1 . . . . .
LAPTOP DELL . 2 3 . . .
;

Solution
‎06-05-2017 06:01 AM
Super User
Posts: 9,227

## Re: Combine observations with same name

Well, a slight modification to @data_null__'s solution here:

https://communities.sas.com/t5/Base-SAS-Programming/code-to-pull-forward-missing-values/m-p/345821#M...

Is this:

```data sales;
input Product \$ Brand \$ January February March April May June;
datalines;
TV SAMSUNG . . 1 . . .
TV SAMSUNG . . . 2 . .
TV SAMSUNG . . . . . 3
TV LG . . 2 . . .
TABLET IPAD . 1 . . . .
TABLET SAMSUNG . . . 5 . .
LAPTOP LENOVO 1 . . . . .
LAPTOP DELL . 2 . . . .
LAPTOP DELL . . 3 . . .
;
run;

proc sort data=sales;
by product brand;
run;

data locf;
update sales(obs=0) sales;
by product brand;
if last.brand then output;
run;
```
New Contributor
Posts: 4

This works fine.

Posts: 3,834

## Re: Combine observations with same name

[ Edited ]

@RW9 The default output for the UPDATE statement is LAST unique key, so you can leave off

```if last.brand then output;
```

It doesn't hurt but not needed.

☑ This topic is solved.