How to achieve Data step output with SQL Joins.

[kuridisanjeev]

Hi..

My requirement sounds pretty simple  but i am finding some difficulties to resolve.

I have two Datasets A and B.

Dataset A:                         Dataset B:

ID           Visit               ID          Amount

100           I                    100          2000

100          II                    100          2500

100          III                   100          3050

102          I                     102          4000

102          II                    102          2000

103          III                   103          7000

If i merge above two datasets using Megre statement in Datastep,the output coming exactly what  i want ,so no problems here.

But if i merge (Join) these two datasets with using SQL  joins(Full Join),SQL  creating Mess due to nature of SQL language.

My big question is how can we achieve the same output produced in dataset with joins??

could  you please guide me how to do.??

Regards.

Sanjeev.K

[DBailey]

If your desired output is:

id     visit     amount

100     i     2000

100     ii     2500

100     iii     3050

102     i     4000

102     ii     2000

103     iii     7000

The only way I see to make proc sql would would be to add a row counter that reset at each change of id which would mimic the data step merge process.

[Ksharp]

Yes. That is most different between data step and sql when you want a many-to-many merge.

The most simple solution is adding a id to identify very single obs.

id+1

after that join it .

KSharp

[LinusH]

It seems you are missing a key. i assume that Visit is part of the key. But how can you be sure that the amount on row one corresponds to visit I?  If you are dependant of original sort order, you  can add the corresponding Visit no to B, then use it as a join key. Another option is to aggregate bot tables first on ID.

[Haikuo · Accepted solution]

Concur with all of the comments made: you will need a unique key for both of your table. You can either make it using date step(physical table or view), or do it on the fly using some undocumented features :

data a;

input (ID Visit) (:$8.); 

cards;

100 I 

100 II 

100 III 

102 I 

102 II 

103 III 

;

data b;

input (ID amount) (:$8.); 

cards;

  100 2000

  100 2500

  100 3050

  102 4000

  102 2000

  103 7000

  ;

  proc sql;

  create table want as

select a.id, a.visit, b.amount from

(select *, monotonic() as obs from a) a,

  (select *,monotonic() as obs from b) b

where a.obs=b.obs;quit;

Haikuo

[kuridisanjeev]

Perfect !!!!!!!!!!!!!!!

Thanks a ton .

Regards

Sanjeev.K