topic Re: Combine two rows into one in SAS Enterprise Guide

Combine two rows into one

lerdem — Thu, 30 Jul 2015 14:05:58 GMT

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

Re: Combine two rows into one

Steelers_In_DC — Thu, 30 Jul 2015 14:14:20 GMT

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;

Re: Combine two rows into one

CurlerBob — Thu, 30 Jul 2015 14:20:48 GMT

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;

Re: Combine two rows into one

RW9 — Thu, 30 Jul 2015 14:41:08 GMT

Hi,

data want (drop=var1);

set have;

var2=coalesce(var1,var2);

run;

Re: Combine two rows into one

Dalveer — Mon, 03 Aug 2015 06:27:54 GMT

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

data need;

set have;

if var1 = '-' then var12 = var2;

else var12=var1;

run;

Re: Combine two rows into one

MadhuKorni — Mon, 03 Aug 2015 09:52:37 GMT

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;