turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SQL Union and data Step

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2014 12:15 AM

Hi All,

I was wondering is there any alternate way to use data step to compare and execute SQL unions techinques. I want to write data step to perform SAS sql unions, like except, interest, union

For example: i have two datasets

Data one; | |

input x A$; | |

datalines; | |

1 | a |

1 | a |

1 | b |

2 | c |

3 | v |

4 | e |

6 | g |

Run; |

Data TWO; | |

input x A$; | |

datalines; | |

1 | x |

2 | y |

3 | z |

3 | v |

5 | w |

Run; |

SQL Except set operator to combine both table vertically. How to get same output in SAS data Step.

proc sql ;

select * from one

except

select * from two;

Quit;

SQL Intersect set operator to combine both table vertically. How to get same output in SAS data Step.

proc sql;

select * from one

**intersect**

select * from two;

Quit;

Thanks in advance.

Accepted Solutions

Solution

07-05-2014
11:21 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SasPro

07-05-2014 11:21 AM

Not sure the value of thinking of datastep operations as set operations by it is not hard, but will require that the data is sorted so that the data step can find the matches. (note that SQL will implicitly sort the data to achieve the same thing.

**data** one;

input x a $ @@;

cards;

1 a 1 a 1 b 2 c 3 v 4 e 6 g

run;

**proc** **sort**; by _all_; **run**;

**data** two;

input x a $ @@;

cards;

1 x 2 y 3 z 3 v 5 w

run;

**proc** **sort**; by _all_; **run**;

**data** except;

merge one (in=inone) two (in=intwo);

by _all_ ;

if inone and not intwo;

**run**;

**data** intercept;

merge one (in=inone) two (in=intwo);

by _all_ ;

if inone and intwo;

**run**;

**data** union;

merge one two ;

by _all_ ;

**run**;

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SasPro

07-05-2014 12:41 AM

proc sort data=one nodupkey;

by x a;

run;

proc sort data=two nodupkey;

by x a;

run;

data except;

merge one(in=b) two(in=c);

by x a;

if b=1 and c=0;

run;

data intersect;

merge one(in=b) two(in=c);

by x a;

if b and c;

run;

Solution

07-05-2014
11:21 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SasPro

07-05-2014 11:21 AM

Not sure the value of thinking of datastep operations as set operations by it is not hard, but will require that the data is sorted so that the data step can find the matches. (note that SQL will implicitly sort the data to achieve the same thing.

**data** one;

input x a $ @@;

cards;

1 a 1 a 1 b 2 c 3 v 4 e 6 g

run;

**proc** **sort**; by _all_; **run**;

**data** two;

input x a $ @@;

cards;

1 x 2 y 3 z 3 v 5 w

run;

**proc** **sort**; by _all_; **run**;

**data** except;

merge one (in=inone) two (in=intwo);

by _all_ ;

if inone and not intwo;

**run**;

**data** intercept;

merge one (in=inone) two (in=intwo);

by _all_ ;

if inone and intwo;

**run**;

**data** union;

merge one two ;

by _all_ ;

**run**;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-29-2014 11:04 AM

Hi,

I mean Can we do Same SQL Unions operations by using Set statement? Using merge it may copy all variables but we can use Keep or drop statement. As Set statement is a concatenating operator

so i am looking to perform Unions by SET statement,