BookmarkSubscribeRSS Feed
brm
Calcite | Level 5 brm
Calcite | Level 5
data store1;
input id product type $;
datalines;
1 112 refferal
2 112 approval
3 112 ship
4 113 ship
;
run;

data store2;
input product productname $;
datalines;
112 xyz
113 abc
115 def
;
run;

proc sort data=store1;
by product;
run;
proc sort data=store2;
by product;

DATA store3;
MERGE store1 store2;
BY product;
RUN:

PROC PRINT DATA=store3;
RUN;

I got output like this

id produc type productname
3 . ship
1 112 refferal xyz
2 112 approval xyz
4 113 ship abc
. 115 def

but i want output to be like this...what changes i have to make.
can u please send me the code.

In final table i want all the rows from dataset1 and productname from table2.

id product productname type
1 112 xyz refferal
2 112 xyz approval
3 112 xyz ship
4 113 abc ship

Thanks,
brm.
17 REPLIES 17
SASJedi
SAS Super FREQ
It looks like you want to keep ALL observations from STORE1 along with any data from STORE2 that happens to match. Observations in STORE2 that do not match a row in STORE1 should be discarded. If so, you need an indication to tell you if the STORE1 data set is contributing to the current observation being processed by the MERGE, and the IN= dataset option is your answer. Try something like this:

DATA store3;
MERGE store1(in=KeepThis) store2;
BY product;
if KeepThis;
RUN;
Check out my Jedi SAS Tricks for SAS Users
brm
Calcite | Level 5 brm
Calcite | Level 5
I would like to get productname from store2,for each product in store1.

Thanks,
brm.
DBailey
Lapis Lazuli | Level 10
proc sql;
create table work.store3 as
select t1.id, t1.product, t2.productname, t1.type
from work.store1 t1 left outer join work.store2 t2 on t1.product=t2.product;
quit;
brm
Calcite | Level 5 brm
Calcite | Level 5
In one dataset product coloum is like this

store1 dataset:

product
00112
00113
00114
00005


store2 dataset:

product
112
113
114.

How to match these two datasets?
SASJedi
SAS Super FREQ
If they are both numeric, the difference is only the format used to display the values. Just merge or join by product, and it will work fine. If not, you'll need to specify the data type for the two columns.
Check out my Jedi SAS Tricks for SAS Users
brm
Calcite | Level 5 brm
Calcite | Level 5
i just want to remove leading zeros in one dataset....so that i can generate new data set by comparing two values.

one dataset is a data base table so i can't edit the format.

Thanks,
brm.
DBailey
Lapis Lazuli | Level 10
Assuming they are both characters (otherwise you wouldn't need to do this):

proc sql;
create table work.final as
select s1.id, s1.product, s1.type, s2.productname
from xxx.store1 left outer join xxx.store2 on
s1.id=put(input(s2.id,3.),z5.);
quit;
DBailey
Lapis Lazuli | Level 10
Assuming they are both characters (otherwise you wouldn't need to do this):

proc sql;
create table work.final as
select s1.id, s1.product, s1.type, s2.productname
from xxx.store1 left outer join xxx.store2 on
s1.id=put(input(s2.id,$3.),z5.);
quit;
brm
Calcite | Level 5 brm
Calcite | Level 5
id coloum is not there in both tables?

product coloum is character in both tables....if i execute query by comparing products getting this error.

ERROR: Unresolved reference to table/correlation name t2.
ERROR: Unresolved reference to table/correlation name t2.
ERROR: Numeric format Z in PUT function requires a numeric argument.
ERROR: Numeric format Z in PUT function requires a numeric argument.

What is the cause for these errors.

Thanks,
brm.
brm
Calcite | Level 5 brm
Calcite | Level 5
DBailey,

It works fine for records without leading zeors....but for some of the products in store1 is having leading zeros,so it is not able to match up with product in store2(product is same without zeors)......i just want to make a change in query to match up product in store1 with leading zeros with product in store2(without zeros).

can we able to handle that change within this query?

Thanks,
brm
Patrick
Opal | Level 21
Hi

From what you describe (and unlike in your sample code) variable "product" must be character (else the leading zeros wouldn't matter).

Below code converts product to numeric for joining:

data store1;
input id product $ type $;
datalines;
1 000112 refferal
2 000112 approval
3 000112 ship
4 000113 ship
;
run;

data store2;
input product $ productname $;
datalines;
112 xyz
113 abc
115 def
;
run;

proc sql;
/* create table want as*/
select store1.*, store2.productname
from store1 left join store2
on input(store1.product,8.)=input(store2.product,8.)
;
quit;


HTH
Patrick
DBailey
Lapis Lazuli | Level 10
Let's try converting them both to numbers:

proc sql;
create table work.final as
select s1.id, s1.product, s1.type, s2.productname
from xxx.store1 left outer join xxx.store2 on
input(s1.id,$5.)=input(s2.id,$5.);
quit;
brm
Calcite | Level 5 brm
Calcite | Level 5
in store1 all products are numeric.
in store2 there are some values for product are strings(ex:AUM,xloera etc).

so if i'm trying to convert both to numeric having issue.....how can i handle this one.

Regards,
brm.
Patrick
Opal | Level 21
Hi

As you can see it would be very worthwhile to provide sample data reflecting the real data in first place....

In the docu for the input function you can find the special meaning for '?'. Changing your code accordingly should solve your problem:
......input(store2.product,? 8.) and input(store2.product,? 8.) ne .


data store1;
input id product $ type $;
datalines;
1 000112 refferal
2 000112 approval
3 000112 ship
4 000113 ship
;
run;

data store2;
input product $ productname $;
datalines;
112 xyz
113 abc
115 def
A15 def
;
run;

proc sql;
/* create table want as*/
select store1.*, store2.productname
from store1 left join store2
on input(store1.product,8.)=input(store2.product,? 8.)
and input(store2.product,? 8.) ne .
;
quit;

HTH
Patrick

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
  • 17 replies
  • 2474 views
  • 0 likes
  • 6 in conversation