Desktop productivity for business analysts and programmers

Combine two rows into one

Reply
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: 858

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: 17

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

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;

Contributor
Posts: 65

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;

Ask a Question
Discussion stats
  • 5 replies
  • 476 views
  • 1 like
  • 6 in conversation