01-30-2017 06:15 AM
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
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
Any1 can advise how i can code this with proc sql or data step in sas? Thanks.
01-30-2017 07:13 AM
If the data is like then:
input avalue $;
length bvalue $200;
infile datalines dlm="¬";
input bvalue $;
distance, 220km ab1234 tune 2000
distance= Bc1234 322km tunez 3000
358kmFd2244 distance tone 3500
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;');
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.
01-30-2017 08:54 AM
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?
Column A (named as "INFO")
distance, 220km ab1234 tune
distance= Bc1234 322km tunez
358kmFd2244 distance tone
Column B (named as "VALUE")
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)
create table set_c as select ID, VALUE
From set_a left join set_b
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?
01-30-2017 09:09 AM
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~