topic Re: Combine observations with same name in SAS Programming

Combine observations with same name

wankata5 — Mon, 05 Jun 2017 06:31:31 GMT

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 . .

Re: Combine observations with same name

RW9 — Mon, 05 Jun 2017 07:55:54 GMT

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;

Re: Combine observations with same name

wankata5 — Mon, 05 Jun 2017 09:31:08 GMT

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 . . .
;

Re: Combine observations with same name

RW9 — Mon, 05 Jun 2017 09:49:11 GMT

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#M79623

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;

Re: Combine observations with same name

wankata5 — Mon, 05 Jun 2017 10:03:05 GMT

Thanks for your quick response.

This works fine.

Re: Combine observations with same name

data_null__ — Mon, 05 Jun 2017 12:37:42 GMT

@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.