DATA Step, Macro, Functions and more

Replace and insert values in dataset

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Replace and insert values in dataset

[ Edited ]

Hi Experts,

 

I am trying to get the below output. I have tried a few things but was not able to get what i wanted.

 

I have two datasets, have1 and have2, i want to search for a string in have1 and insert values from dataset 2 when there is a specific value.

 

 

data have1;
length name $5;
input name ;
datalines;
ABC
EFD
-----
FGH
;
run;

data have2;
length filler $5;
input filler;
datalines;
FFF
GGG
HHH
;
run;

the final dataset want will be used to write out to a text file.

 

Is it possible? 

thanks in advance.

 

Manjeet


Cheers from India!

Manjeet

Accepted Solutions
Solution
‎01-19-2017 04:17 AM
Super User
Posts: 6,932

Re: Replace and insert values in dataset

OK, so the ----- is one observation and not a certain sequence of observations.

Based on what I remember from your original intended result, this should work:

data want;
set have1;
if name = '-----'
then do until (done);
  set have2 end=done;
  name=filler;
  output;
end;
else output;
drop filler;
run;

This will fail if you have more than one "-----" observation.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 6,932

Re: Replace and insert values in dataset

Please use a data step and post complete example data, so it is easier to recreate the datasets and understand the issue.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 52

Re: Replace and insert values in dataset

done.


Cheers from India!

Manjeet
Solution
‎01-19-2017 04:17 AM
Super User
Posts: 6,932

Re: Replace and insert values in dataset

OK, so the ----- is one observation and not a certain sequence of observations.

Based on what I remember from your original intended result, this should work:

data want;
set have1;
if name = '-----'
then do until (done);
  set have2 end=done;
  name=filler;
  output;
end;
else output;
drop filler;
run;

This will fail if you have more than one "-----" observation.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,081

Re: Replace and insert values in dataset

A variation for multiple "-----" values:

 

data want;

set have1;

if name = '-----' then do i=1 to _nobs_;

   set have2 point=i nobs=_nobs_;

   name=filler;

   output;

end;

else output;

drop filler;

run;

Contributor
Posts: 52

Re: Replace and insert values in dataset

@KurtBremser

 

thanks Kurt. this is exactly what i was looking for.

 

Cheers from India!

 

Manjeet


Cheers from India!

Manjeet
Super Contributor
Posts: 474

Re: Replace and insert values in dataset

[ Edited ]

Hi.

 

If you won't mind transforming have2 by including a key and index, you could try this which will work for every '-----' found in have1:

 

* create key and index over have2;
data have3 (index=(NAME));
set have2;
NAME='-----';
run;

data want;
set have1;
drop FILLER;

do until (_ERROR_); * cycle through have2;
   set have3 key=NAME; 
   if not _ERROR_ then do; * match;	  
      NAME=FILLER;
      output;
	  NAME='-----';	  
   end;
   else if NAME ne '-----' then output; * no match;
end;
_ERROR_=0;
run;

What you are trying to do is actually a left join. 

 

The code above is an old technique which uses an index to reset the cursor over have2 when the key will not match.

 

Other similar approaches could be:

 

Load have2 into an hash table and iterate through it.

SQL left join of have1 with have2.

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 208 views
  • 5 likes
  • 4 in conversation