Solved
Contributor
Posts: 29

How to apply multiple set property using proc sql?

Input -

I have two datasets -

Data Tab1;

input id x \$;

cards;

1 a

2 b

3 c

4 d

5 e

;

run;

Data Tab2;

input y \$;

hh

ii

jj

uu

kk

;

Outuput -

Tab3 -

id x y

1 a hh

2 b ii

3 c jj

4 d uu

5 e kk

It can be done by using multiple set statement. But, how can we do it by using proc sql?

Accepted Solutions
Solution
‎02-06-2015 05:48 AM
Super User
Posts: 9,599

Re: How to apply multiple set property using proc sql?

Firstly I don't recommend merging data based on an adhoc position in a dataset.  If the sequence changes, say extra rows added, or sorted differently you will end up with different outcome, hence this kind of merge is dangerous.

If however you are set on this course of action:

proc sql;

create table WANT as

select     A.ID,

A.X,

B.Y

from       (select *,MONOTONIC() as _N from DS1) A

left join  (select *,MONOTONIC() as _N from DS2) B

on          A._N=B._N;

quit;

Note the monotonic function is currently unsupported and may return un-expected results.  The underlying fact about merging is that there should be a logical set of matching identifier variables in each table to match against.

All Replies
Solution
‎02-06-2015 05:48 AM
Super User
Posts: 9,599

Re: How to apply multiple set property using proc sql?

Firstly I don't recommend merging data based on an adhoc position in a dataset.  If the sequence changes, say extra rows added, or sorted differently you will end up with different outcome, hence this kind of merge is dangerous.

If however you are set on this course of action:

proc sql;

create table WANT as

select     A.ID,

A.X,

B.Y

from       (select *,MONOTONIC() as _N from DS1) A

left join  (select *,MONOTONIC() as _N from DS2) B

on          A._N=B._N;

quit;

Note the monotonic function is currently unsupported and may return un-expected results.  The underlying fact about merging is that there should be a logical set of matching identifier variables in each table to match against.

Super User
Posts: 10,766

Re: How to apply multiple set property using proc sql?

You need to create a index variable ID in Tab1 as in Tab2;

Data Tab1;

input id x \$;

cards;

1 a

2 b

3 c

4 d

5 e

;

run;

Data Tab2;

input y \$;

cards;

hh

ii

jj

uu

kk

;

data tab2;

set tab2;

id+1;

run;

proc sql;

create table want as

select *

from tab1 natural join tab2 ;

quit;

Xia Keshan

Super User
Posts: 10,214

Re: How to apply multiple set property using proc sql?

Use the right tool, which in this case is the data step with a merge; no need for SQL.

Of course you can try to put the screw into the wood by detonating a pound of dynamite on top of it, but a screwdriver is the better option.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,599

Re: How to apply multiple set property using proc sql?

To re-write an old favourite - select needle from haystack

How about - insert screw into wood

What could be simpler?

🔒 This topic is solved and locked.