Hi Good day~
I have the following sas dataset A and excel B that i need to map the value onto set A in sas.
Set A: example
ab1234
Bc1234
Fd2244
Set B: example
"distance, 220km ab1234 tune 2000"
"distance= Bc1234 322km tunez 3000"
"358kmFd2244 distance tone 3500"
I need to have the values 2000, 3000 and 3500 which i had split to another excel column and imported to sas, and will need put the values corresponding to set A
i.e.
Ab1234 2000
Bc1234 3000
Fd2244 3500
Any1 can advise how i can code this with proc sql or data step in sas? Thanks.
If the data is like then:
data a;
input avalue $;
datalines;
ab1234
Bc1234
Fd2244
;
run;
data b;
length bvalue $200;
infile datalines dlm="¬";
input bvalue $;
datalines;
distance, 220km ab1234 tune 2000
distance= Bc1234 322km tunez 3000
358kmFd2244 distance tone 3500
;
run;
data _null_;
set a end=last;
if _n_=1 then call execute('data want; set b;');
call execute('if index(bvalue,"'||strip(avalue)||'") > 0 then do;
aval="'||strip(avalue)||'"; dist=substr(bvalue,lengthn(bvalue)-4); output; end;');
if last then call execute('run;');
run;
This generates a datastep after it has finished, which has one row per obs in data a, as index() to find the string, and then set some values. Note how I have put test data in datasteps - please remember this for future posts.
Hi thanks looks complicated on the last bits... i will try it out when back in office...
What if my dataset is like below instead? Any simpler proc sql i can utilize?
Set A:
ID
Ab1234
Bc1234
Fd2244
Set B:
Column A (named as "INFO")
distance, 220km ab1234 tune
distance= Bc1234 322km tunez
358kmFd2244 distance tone
Column B (named as "VALUE")
2000
3000
3500
I juz want to put the value from Set B onto Set A but have to look for the unique Set A ID from the swamped or jumbled Set B.
Is proc sql possible or easier? Got 10 of thousands of variables under both sets.
Normally i had the same unique set a and unique set b ID data to map with but this new Set b is different. For the same unique data i juz use proc sql as follows:
(For same unique id)
Proc sql;
create table set_c as select ID, VALUE
From set_a left join set_b
On a.id=b.info;run;
Since this new Set B is jumbled, is it possible to just
use "on a.id like b.%info%" ?? Or "in" function?
Wonder if that will work for proc sql?
Hmmm ignore for the time being...need look at the set b variables when back in office....ciz i think the set b might b the below..
Coz Inside set b could be ab12 34, ab 12 34, bc1 234, fd223 4...with other numbers and characters also....sigh~
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.