Error while merging two dataset proc sql : URGENT HELP

srmakwana2009 — Sun, 24 Jul 2022 05:07:03 GMT

I am trying to merge many to many variables using proc sql. i am getting syntex error . can anyone please help what is syntex error is? please help. it's urgent

BELOW IS MY CODE

proc sort data=pk_dose0;
by nsid date time dose;
run;

proc sort data=dose_poppk;
by nsid date time dose;
run;

proc sql;
create table aa as
select * from pk_dose0 as a join dose_poppk as b on
(a.nsid=b.nsid)
(a.date=b.date)
(a.time=b.time);
quit;

proc sql;
create table both as
select
coalesce(a.nsid, b.nsid) as id,
coalesce(a.date, b.date) as date,
coalesce(a.time, b.time) as time,
farm
from
pk_dose0 as a full join
dose_poppk as b
on a.id = b.id,
on a.date = b.date,
on a.time = b.time);
quit;

BELOW IS SCREENSHOT OF ERROR.

Re: Error while merging two dataset proc sql : URGENT HELP

ballardw — Sun, 24 Jul 2022 13:04:21 GMT

Commas in the logic instead of how to add the comparison logic. Only one ON per join and you use AND and OR, possibly with ( ) to group a condition to add to any comparison whether an ON( or WHERE) clause.

proc sql;
create table both as
select
coalesce(a.nsid, b.nsid) as id,
coalesce(a.date, b.date) as date,
coalesce(a.time, b.time) as time,
farm
from
pk_dose0 as a full join
dose_poppk as b
on a.id = b.id
    and a.date = b.date
    and a.time = b.time;
quit;

However you have another syntax problem with a ) after = b.time that does not have a matching (.

You likely have an additional logic issue understanding your problem: Use of coalesce(a.time, b.time) coupled with the join on a.time=b.time. With the JOIN requirement A.time would be the same as B.time always so the coalesce is basically not adding anything. IF you expect to have a missing A.time or B.time where coalesce would make sense then your JOIN criteria have to be significantly different. Same for the A.date and B.date.

You did not actually describe what sort of comparison you want. A danger with posting code that does not work without a description of what is needed is that we will quite often assume that the simplest thing that cleans up errors is what is needed.

It may be that what you wanted could be:

proc sql;
create table both as
select
coalesce(a.nsid, b.nsid) as id,
coalesce(a.date, b.date) as date,
coalesce(a.time, b.time) as time,
farm
from
pk_dose0 as a full join
dose_poppk as b
on a.id = b.id
    and (a.date = b.date
         or a.time = b.time
        )
 ;
quit;

This would be match on date or the time. But this might be a poor choice if you have the same times on different dates along with the same time on missing dates for the same Id variable values.

If you actually expect to have missing values and then align them some how you need to provide example data of the two sets and how you expect the result to look after the match along with any matching rules.

Save yourself some time and instead of creating a PICTURE of the log just copy the text from the log. The on the forum open a text box using the </> that appears above the message window and paste the text.

Re: Error while merging two dataset proc sql : URGENT HELP

tarheel13 — Sun, 24 Jul 2022 12:08:27 GMT

In the join condition instead of , put 'and'

topic Error while merging two dataset proc sql : URGENT HELP in New SAS User

Error while merging two dataset proc sql : URGENT HELP

Re: Error while merging two dataset proc sql : URGENT HELP

Re: Error while merging two dataset proc sql : URGENT HELP