DATA Step, Macro, Functions and more

How to apply multiple set property using proc sql?

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

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
Super User
Posts: 7,401

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.

View solution in original post


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

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: 9,676

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: 6,933

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
Super User
Super User
Posts: 7,401

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 211 views
  • 0 likes
  • 4 in conversation