topic Re: merging datasets to generate new column in SAS Programming

merging datasets to generate new column

brm — Thu, 16 Jun 2011 21:39:15 GMT

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.

Re: merging datasets to generate new column

SASJedi — Fri, 17 Jun 2011 01:55:51 GMT

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;

Re: merging datasets to generate new column

brm — Fri, 17 Jun 2011 02:58:28 GMT

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

Thanks,
brm.

Re: merging datasets to generate new column

DBailey — Fri, 17 Jun 2011 12:18:51 GMT

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;

Re: merging datasets to generate new column

brm — Fri, 17 Jun 2011 22:00:14 GMT

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?

Re: merging datasets to generate new column

SASJedi — Sat, 18 Jun 2011 02:21:29 GMT

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.

Re: merging datasets to generate new column

brm — Sat, 18 Jun 2011 04:58:04 GMT

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.

Re: merging datasets to generate new column

DBailey — Sat, 18 Jun 2011 13:13:54 GMT

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;

Re: merging datasets to generate new column

DBailey — Sat, 18 Jun 2011 13:14:24 GMT

Re: merging datasets to generate new column

Ankitsas — Sat, 18 Jun 2011 18:16:29 GMT

Dude,

The code is as follows , you will get what you need:-

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;
run;

DATA store3;
MERGE store1(in=a) store2(in=b);
BY product;
if a;
RUN;

Re: merging datasets to generate new column

brm — Sun, 19 Jun 2011 04:55:22 GMT

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.

Re: merging datasets to generate new column

brm — Sun, 19 Jun 2011 06:35:26 GMT

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

Re: merging datasets to generate new column

Patrick — Sun, 19 Jun 2011 11:12:19 GMT

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

Re: merging datasets to generate new column

DBailey — Sun, 19 Jun 2011 12:49:22 GMT

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;

Re: merging datasets to generate new column

brm — Mon, 20 Jun 2011 03:01:32 GMT

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.

Re: merging datasets to generate new column

Patrick — Mon, 20 Jun 2011 03:45:05 GMT

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

Re: merging datasets to generate new column

Ksharp — Mon, 20 Jun 2011 08:15:41 GMT

[pre]

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;
data store11;
set store1;
product=substr(product,verify(product,'0'));
run;

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

DATA store3;
MERGE store11(in=one) store2;
BY product;
if one;
RUN;

[/pre]

Or

[pre]

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 a.* ,b.productname
from store1 as a left join store2 as b on a.product contains strip(b.product)
;
quit;

[/pre]

Ksharp Message was edited by: Ksharp

Re: merging datasets to generate new column

brm — Mon, 20 Jun 2011 18:35:25 GMT

Thanks everybody.......it worked.....sorry for the confusion.

Thanks,
brm.