topic Treat Missing values in SAS Procedures

Treat Missing values

jhh197 — Mon, 20 Jun 2016 20:51:39 GMT

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

Re: Treat Missing values

Astounding — Mon, 20 Jun 2016 23:09:00 GMT

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.

Re: Treat Missing values

jhh197 — Mon, 20 Jun 2016 23:59:57 GMT

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	.

Re: Treat Missing values

Ksharp — Tue, 21 Jun 2016 02:24:16 GMT

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;

Re: Treat Missing values

jhh197 — Tue, 21 Jun 2016 17:46:09 GMT

Hi Keshan,

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