DATA Step, Macro, Functions and more

Combine observations with same name

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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
Super User
Posts: 7,942

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

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
Super User
Posts: 7,942

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

Re: Combine observations with same name

Thanks for your quick response. 

 

This works fine.

Respected Advisor
Posts: 3,799

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.

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

Discussion stats
  • 5 replies
  • 151 views
  • 1 like
  • 3 in conversation