Merge and By Variable Issue

yudhishtirb — Fri, 09 Sep 2016 12:59:48 GMT

Hello Team,

I am dealing with multiple datasets in one of my program, Recently I added one dataset which dont have column used in merge operation earlier. So I added empty column as below

proc sql;
create table Emfuleni.GroupCodeTemp as
select '' AS Ward_Description, wGroup, szAccountNumber
from db.GroupChange
WHERE wClientCode = -10023 ORDER BY szAccountNumber;
quit;

DATA GroupCode;
SET Emfuleni.GroupCodeTemp;
BY szAccountNumber;
IF FIRST.szAccountNumber THEN count=1;
else count+1;
IF count>2 THEN delete;
drop count;
RUN;

In above query Ward_Description is empty column added. Now I am using this dataset in one of merge operation as below

data Emfuleni.lastjoin;
merge Emfuleni.ptp Emfuleni.masterward(in = a where = (open eq 1)) Emfuleni.Arrears Emfuleni.groupcode ;
by Ward_Description szAccountNumber;
if a;
run;

But i am always getting error as below

ERROR: BY variable Ward_Description is not on input data set EMFULENI.GROUPCODE.

Can someone please assist me what going wrong here ?

Thanks In Advance

Re: Merge and By Variable Issue

ballardw — Fri, 09 Sep 2016 14:49:47 GMT

Since you are using this code:

DATA GroupCode;
SET Emfuleni.GroupCodeTemp;
BY szAccountNumber;
IF FIRST.szAccountNumber THEN count=1;
else count+1;
IF count>2 THEN delete;
drop count;
RUN;

I suspect that this line

merge Emfuleni.ptp Emfuleni.masterward(in = a where = (open eq 1)) Emfuleni.Arrears Emfuleni.groupcode ;

refers to an earlier version of the data set Emfuleni.groupcode and you mean to use either:

merge Emfuleni.ptp Emfuleni.masterward(in = a where = (open eq 1)) Emfuleni.Arrears Groupcode ;

or to name the data set Groupcode as Emfuleni.groupcode.

With your Ward_Description as all missing (blank) in Groupcode I suspect that your results may not be what you want for the LastJoin.

topic Merge and By Variable Issue in SAS Enterprise Guide

Merge and By Variable Issue

Re: Merge and By Variable Issue