Desktop productivity for business analysts and programmers

Merge and By Variable Issue

Reply
Contributor
Posts: 25

Merge and By Variable Issue

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

Super User
Posts: 11,107

Re: Merge and By Variable Issue

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.

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 214 views
  • 0 likes
  • 2 in conversation