DATA Step, Macro, Functions and more

Need help with SAS!

Reply
New Contributor
Posts: 3

Need help with SAS!

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.

Super User
Super User
Posts: 7,976

Re: Need help with SAS!

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.

New Contributor
Posts: 3

Re: Need help with SAS!

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?

New Contributor
Posts: 3

Re: Need help with SAS!

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~

Ask a Question
Discussion stats
  • 3 replies
  • 107 views
  • 0 likes
  • 2 in conversation