topic Re: Replace and insert values in dataset in SAS Programming

Replace and insert values in dataset

mnjtrana — Mon, 16 Jan 2017 08:11:06 GMT

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

Re: Replace and insert values in dataset

Kurt_Bremser — Mon, 16 Jan 2017 07:40:40 GMT

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

Re: Replace and insert values in dataset

mnjtrana — Mon, 16 Jan 2017 08:11:30 GMT

done.

Re: Replace and insert values in dataset

Kurt_Bremser — Mon, 16 Jan 2017 08:23:03 GMT

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.

Re: Replace and insert values in dataset

DanielSantos — Mon, 16 Jan 2017 10:04:35 GMT

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

Re: Replace and insert values in dataset

Astounding — Mon, 16 Jan 2017 13:18:11 GMT

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;

Re: Replace and insert values in dataset

mnjtrana — Thu, 19 Jan 2017 09:18:46 GMT

@Kurt_Bremser

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

Cheers from India!

Manjeet