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
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
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.
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.