Need help with SAS!

[CayCe]

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.

[RW9]

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.

[CayCe]

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?

[CayCe]

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~