topic Re: How to apply multiple set property using proc sql? in SAS Programming

How to apply multiple set property using proc sql?

nikunjgattani — Fri, 06 Feb 2015 10:24:52 GMT

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 $;

;

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?

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

RW9 — Fri, 06 Feb 2015 10:48:25 GMT

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.

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

Ksharp — Fri, 06 Feb 2015 10:51:10 GMT

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;

;

data tab2;

set tab2;

id+1;

run;

proc sql;

create table want as

select *

from tab1 natural join tab2 ;

quit;

Xia Keshan

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

Kurt_Bremser — Fri, 06 Feb 2015 13:04:02 GMT

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.

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

RW9 — Fri, 06 Feb 2015 14:10:45 GMT

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

How about - insert screw into wood

What could be simpler?