Sample 51570: How to replicate the DATA step MERGE statement in DS2
The DS2 language does not support the MERGE statement commonly used in the DATA step language until the third maintenance release of SAS® 9.4 (TS1M3). However, the same results can be generated prior to that release by embedding SQL code within the DS2 SET statement, as shown in the sample code on the Full Code tab.
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
The SQL code embedded in a DS2 SET statement must be enclosed in braces.
data one;
input id name :$5.;
datalines;
1 Mark
2 Scott
3 Amy
3 Fred
4 Joe
;
run;
data two;
input id car :$10.;
datalines;
1 Maserati
1 Ford
2 Ferrari
3 Bugatti
;
run;
proc sort data=one;
by id;
run;
proc sort data=two;
by id;
run;
data three;
merge one two;
by ID;
run;
title 'Base SAS DATA Step merge';
proc print data=three noobs;
run;
title 'DS2 merge with SQL embedded in a DS2 SET statement';
proc ds2;
data;
method run();
set {select a.*, car from one as a full join two as b on a.id=b.id};
by ID;
end;
enddata;
run;
quit;
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
Base SAS DATA Step merge
id name car
1 Mark Maserati
1 Mark Ford
2 Scott Ferrari
3 Amy Bugatti
3 Fred Bugatti
4 Joe
--------------------------------------------------------------------------------
DS2 merge with SQL embedded in a DS2 SET statement
id name car
1 Mark Maserati
1 Mark Ford
2 Scott Ferrari
3 Amy Bugatti
3 Fred Bugatti
4 Joe
The DS2 language does not support the MERGE statement until the third maintenance release of SAS
® 9.4 (TS1M3), but the same results can still be generated with alternative code.
| Date Modified: | 2017-11-06 14:54:48 |
| Date Created: | 2013-11-11 10:42:02 |
Operating System and Release Information
| SAS System | Base SAS | Microsoft Windows 8.1 Enterprise 32-bit | 9.4 TS1M0 | |
| Microsoft Windows 8 Pro x64 | 9.4 TS1M0 | |
| Microsoft Windows 8 Pro 32-bit | 9.4 TS1M0 | |
| Microsoft Windows 8 Enterprise x64 | 9.4 TS1M0 | |
| Microsoft Windows 8 Enterprise 32-bit | 9.4 TS1M0 | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.4 TS1M0 | |
| Microsoft® Windows® for x64 | 9.4 TS1M0 | |
| Z64 | 9.4 TS1M0 | |
| z/OS | 9.4 TS1M0 | |
| Microsoft Windows 8.1 Enterprise x64 | 9.4 TS1M0 | |
| Microsoft Windows 8.1 Pro | 9.4 TS1M0 | |
| Microsoft Windows 8.1 Pro 32-bit | 9.4 TS1M0 | |
| Microsoft Windows Server 2008 R2 | 9.4 TS1M0 | |
| Microsoft Windows Server 2008 for x64 | 9.4 TS1M0 | |
| Microsoft Windows Server 2012 Datacenter | 9.4 TS1M0 | |
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 TS1M0 | |
| Microsoft Windows Server 2012 R2 Std | 9.4 TS1M0 | |
| Microsoft Windows Server 2012 Std | 9.4 TS1M0 | |
| Windows 7 Enterprise x64 | 9.4 TS1M0 | |
| Windows 7 Professional x64 | 9.4 TS1M0 | |
| 64-bit Enabled AIX | 9.4 TS1M0 | |
| 64-bit Enabled HP-UX | 9.4 TS1M0 | |
| 64-bit Enabled Solaris | 9.4 TS1M0 | |
| HP-UX IPF | 9.4 TS1M0 | |
| Linux for x64 | 9.4 TS1M0 | |
| Solaris for x64 | 9.4 TS1M0 | |
