Treat Missing values

[jhh197]

Hi ,

I have a table a and Table b and i how to get table c . Can anyone pls help

Table a

item id     score

dis     1       351

ins     2       2.12

ins     3       2.15

ins     4       0.05

gbs     5       2.0

gbs     6       6.0

and table b

ins   gbs

3       7

2       5

5       6

and how to get table c

dis	ins	gbs
351	2.15	6
351	2.12	2
351	0.05	6

and how do i change below query so that i get table c without missing values

proc sql;
create table xyz as
 select (select sale_amt from a where item='dis') as dis,
       a.ins as x_id, b.sale_amt as ins,
       a.gbs as y_id,c.sale_amt as gbs

from b as a

left join
(select * from a where item='ins') as b
on a.ins=b.id
left join
( select * from a where item='gbs') as c
on a.gbs=c.id
;
quit;

and  when ins and gbs values in b table are not in id coloumn for table a we take the value from previous one like we dont have gbs 7 in table a so we take the value for for gbs 6 which is 6.0

[Astounding]

At a minimum, you will have to post more data.  Your SELECT statement refers to SALE_AMT, but there is no such field anywhere.

At the same time, the SQL code tries to extract from table c.  But the problem states that table c is the output.

it might be easier to figure this out if you describe your intentions.  Posting nonworking code might not be enough in this case.

[jhh197]

Hi ,

Sure

My table A

item	id	score
dis	1	351
ins	2	2.12
ins	3	2.15
ins	4	0.05
gbs	5	2
gbs	6	6

Table B

ins	gbs
3	7
2	5
5	6

and when i execute below query i get Table C as output but i get missing values for gbs 7 so it should take value from 6 which is 6  and ins 5 is missing so it should take from 3  which is 2.15

proc sql;
create table want as
select (select score from a  where item='dis') as dis,
       a.ins as dxnum,b.score as ins,
       a.gbs as hxnum,c.score as gbs
from
B as a
left join
(select * from a where item='ins') as b
on  a.ins = b.id
left join
(select * from a where item='gbs') as c
on a.gbs=c.id;
quit;

TABLE C

dis	dxnum	ins	hxnum	gbs
351	2	2.12	5	2
351	5	.	6	6
351	3	2.15	7	.

[Ksharp · Accepted solution]

data a;
infile cards expandtabs;
input item $ id sale_amt ;
cards;
dis     1       351
ins     2       2.12 
ins     3       2.15 
ins     4       0.05 
gbs     5       2.0
gbs     6       6.0
;
run;
data b;
infile cards expandtabs;
input ins   gbs;
cards;
3       7
2       5
5       6
;
run;



proc sql;
create table xyz as
 select (select sale_amt from a where item='dis') as dis,
       a.ins as x_id, b.sale_amt as ins,
       a.gbs as y_id,c.sale_amt as gbs
from b as a
left join
(select * from a where item='ins') as b
on a.ins=b.id
left join
( select * from a where item='gbs') as c
on a.gbs=c.id
;

create table want as
 select (select sale_amt from a where item='dis') as dis,
       x_id, 
       coalesce(ins,(select sale_amt from a having abs(x_id-id)=min(abs(x_id-id)))) as ins,
       y_id,
       coalesce(gbs,(select sale_amt from a having abs(y_id-id)=min(abs(y_id-id)))) as gbs
from xyz;
quit;
 

[jhh197]

Hi Keshan,

Thank you so much for your help . Thanks a lot .