topic How to achieve Data step output with SQL Joins. in SAS Programming

How to achieve Data step output with SQL Joins.

kuridisanjeev — Wed, 24 Apr 2013 11:24:09 GMT

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

Re: How to achieve Data step output with SQL Joins.

DBailey — Wed, 24 Apr 2013 12:03:18 GMT

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.

Re: How to achieve Data step output with SQL Joins.

Ksharp — Wed, 24 Apr 2013 12:05:18 GMT

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

Re: How to achieve Data step output with SQL Joins.

LinusH — Wed, 24 Apr 2013 12:06:07 GMT

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.

Re: How to achieve Data step output with SQL Joins.

Haikuo — Wed, 24 Apr 2013 15:55:21 GMT

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

Re: How to achieve Data step output with SQL Joins.

kuridisanjeev — Thu, 25 Apr 2013 11:50:11 GMT

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

Thanks a ton .

Regards

Sanjeev.K