DATA Step, Macro, Functions and more

Concatenate a variable, can i then reverse the process?

Reply
Contributor
Posts: 58

Concatenate a variable, can i then reverse the process?

I'm concatenating a variable to make the process of merging 2 datasets together go a lot faster, but once this is done, i'd really like to get things back to the original variables. Is this possible?
PROC Star
Posts: 7,363

Re: Concatenate a variable, can i then reverse the process?

Charles,

There are at least two answers. One, you don't have to drop the original variables in the first place. Two, if you concatenate with a uniques separator (e.g., a |), you can always use the scan function to deconcatenate.

Art
SAS Employee
Posts: 104

Re: Concatenate a variable, can i then reverse the process?

Charles,
I'd recommend an SQL solution as easier to code. Consider this code:

**********************************************************************;
/*Create two data sets to play with*/
data DS1;
LENGTH Text $1 Num 8 DS1 $1;
DS1="X";
do Text= "A","B","C";
do Num=1 to 2;
output;
end;
end;
run;
data DS2;
LENGTH Text $1 Num 8 DS2 $1;
DS2="X";
do Text= "A","C";
do Num=1 to 5;
output;
end;
end;
run;

/* SQL solution */
proc sql;
create table Merged_sql as
select coalesce(A.TEXT,B.TEXT,"") AS Text
, coalesce(a.Num,B.Num) as Num
, DS1
, DS2
from ds1 as a
full JOIN
ds2 as b
on a.Text=b.Text and a.Num=b.num
;
quit;
**********************************************************************;

You can produce the same results with the following data step code & concatenating variables:

**********************************************************************;
/*Data Step Solution*/
data DS1a;
set DS1;
_joinvar=CATX(Text,Num);
run;
proc sort data=DS1a;
by _joinvar;
run;

data DS2a;
set DS2;
_joinvar=CATX(Text,Num);
run;
proc sort data=DS2a;
by _joinvar;
run;

data Merged_data_step;
merge DS1a
DS2a ;
by _joinvar;
drop _joinvar;
run;

proc sort data=merged_data_step;
by Text Num;
run;
**********************************************************************;

Hope this helps!
Ask a Question
Discussion stats
  • 2 replies
  • 701 views
  • 0 likes
  • 3 in conversation