BookmarkSubscribeRSS Feed
CayCe
Calcite | Level 5

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.

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Calcite | Level 5

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~

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 708 views
  • 0 likes
  • 2 in conversation