Change multiple observations in a variable
data a1
col1 col2 flag
a 2 .
b 3 .
a 4 .
c 1 .
For data a1
, flag
always missing. I want to update multiple lines with a2
.
data a2
col1 flag
a 1
Ideal way out:
col1 col2 flag
a 2 1
b 3 .
a 4 1
c 1 .
But this does not update all entries in the instruction by
.
data a1;
modify a1 a2;
by col1;
run;
Modified question
In fact a1
- a very large set of data on the server. So I prefer modifying it (if possible) instead of creating a new one. Otherwise, I need to drop the previous one first a1
and copy the new one a1
from local to server, which will take much longer.
source to share
If you want to do this with MODIFY, you have to loop through the modification dataset in some way, or it will only replace the first row (because the other dataset will then end up with records - this usually behaves like a merge, where as soon as it finds a match, it moves on to the next entry). Here is one option - there are others.
data a1(index=(col1));
input col1 $ col2 flag;
datalines;
a 2 .
b 3 .
a 4 .
c 1 .
;;;;
run;
data a2(index=(col1));
col1='a';
flag=1;
run;
data a1;
set a2(rename=flag=flag2);
do _n_ = 1 to nobs_a1;
modify a1 key=col1 nobs=nobs_a1;
if _iorc_=0 then do;
flag=flag2;
replace;
end;
end;
if _iorc_=%sysrc(_DSENOM) then _error_=0;
run;
source to share
If you are not using the Merge operator for the sorting problem, you can simply change your approach to merging. If the flag is always absent in A1, you can remove it, otherwise you must temporarily rename it so as not to lose this data. Here I will be concatenating A1 and A2 using hash objects, this approach does not require pre-sorting on datasets.
data final_merged(drop = finder);
length flag 8.; /*please change length with the real one, use $ if char*/
if _N_ = 1 then do;
declare hash merger(dataset:'A2');
merger.definekey('col1');
merger.DefineData ('flag');
merger.definedone();
end;
set A1(drop=flag);
finder = merger.find();
if finder ne 0 then flag = .;
/*then flag='' or then flag='unknown' as you want if flag is a character var*/
run;
Please let me know if this helps.
source to share
You can do the following, but SQL sorts the observations, so not sure how useful it is to you? (you can always preprocess ordvar=_n_;
and then sort the SQL statement on it if that helps):
Data:
data a1 ;
input col1 $ col2 flag ;
cards ;
a 2 .
b 3 .
a 4 .
c 1 .
;run ;
data a2 ;
input col1 $ flag ;
cards ;
a 1
;run ;
Merge:
proc sql ;
create table output as
select a.col1, a.col2, b.flag
from a1 a
left join
a2 b
on a.col1=b.col1
;quit ;
source to share
To try and do this in one pass, how do I create two macro variables containing the mapping from a2
?
proc sql ;
select distinct col1, flag
into :colvals separated by '', :flagvals separated by ''
from a2
;quit ;
Set the flag to the appropriate character position between the two macro variables:
data a1 ;
set a1 ;
if findc("&colvals",col1) then
flag=input(substr("&flagvals", findc("&colvals",col1),1),8.) ;
run ;
source to share