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
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.
Please use a data step and post complete example data, so it is easier to recreate the datasets and understand the issue.
done.
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.
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;
thanks Kurt. this is exactly what i was looking for.
Cheers from India!
Manjeet
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.