Solved
Contributor
Posts: 40

# Treat Missing values

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

Accepted Solutions
Solution
‎06-21-2016 01:44 PM
Super User
Posts: 10,770

## Re: Treat Missing values

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

All Replies
Super User
Posts: 6,761

## Re: Treat Missing values

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.

Contributor
Posts: 40

## Re: Treat Missing values

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 .

Solution
‎06-21-2016 01:44 PM
Super User
Posts: 10,770

## Re: Treat Missing values

``````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;
``````
Contributor
Posts: 40

## Re: Treat Missing values

Hi Keshan,

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

🔒 This topic is solved and locked.