merging datasets to generate new column

[brm]

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.

[SASJedi]

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;

[brm]

I would like to get productname from store2,for each product in store1.

Thanks,
brm.

[DBailey]

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]

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]

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.

[brm]

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]

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]

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]

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]

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]

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]

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]

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]

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