Contributor
Posts: 47

Combine two rows into one

Data set i have is as below:

var1  var2    var3  var4

A      -        32     12

B      -        25      14

-       C       -       18

-        D       15     13

i need to combine var1 and var2

var12   var3   var4

A       32      12

B        25       14

C        -         18

D        15        13

Valued Guide
Posts: 864

Re: Combine two rows into one

I'm assuming there is a lot more that goes along with this.  Here is a solution for you, but based on any other nuances to the data or size of data there is a chance there is a much better solution:

data have;

infile cards dsd;

input var1\$  var2\$    var3  var4;

cards;

A,,32,12

B,,25,14

,C,,18

,D,15,13

;

run;

data want;

set have;

if not missing(var1) then var12 = var1;

if not missing(var2) then var12 = var2;

drop var1 var2;

run;

Occasional Contributor
Posts: 19

Re: Combine two rows into one

I'd use a pretty simple proc sql.  The coalesce function will take the first non-missing value.  If the "-" is actually "" in your data, the following will work.

proc sql;

create table NewDataset as

select coalesce(var1, var2) as var12,

var3,

var4

from OriginalDataset;

quit;

if the field is actually populated with "-" then a simple reassignment statement will work:

if var1 = '-' then var12 = var1;

else var12 = var2;

Super User
Posts: 9,829

Re: Combine two rows into one

Hi,

data want (drop=var1);

set have;

var2=coalesce(var1,var2);

run;

Occasional Contributor
Posts: 10

Re: Combine two rows into one

try this below code: give you desired output:-->

data need;

set have;

if var1 = '-' then var12 = var2;

else var12=var1;

run;

Frequent Contributor
Posts: 79

Re: Combine two rows into one

data want;

set have;

var12=compress(catt(var1,var2),'-'); *If you want to remove only '-' after combining;

var11=compress(catt(var1,var2),,'ka'); *If you want to delete any special characters and digits after combining;

drop var1 var2;

run;

Discussion stats
• 5 replies
• 550 views
• 1 like
• 6 in conversation