BookmarkSubscribeRSS Feed
CharlesR
Calcite | Level 5
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?
2 REPLIES 2
art297
Opal | Level 21
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
SASJedi
SAS Super FREQ
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!
Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3313 views
  • 0 likes
  • 3 in conversation