Help using Base SAS procedures

Treat Missing values

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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

 

disinsgbs
3512.156
3512.122
3510.056

 

 

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,028

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;
 

View solution in original post


All Replies
Super User
Posts: 5,509

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: 37

Re: Treat Missing values

Posted in reply to Astounding

Hi ,

 

Sure

My table A

 

itemidscore
dis1351
ins22.12
ins32.15
ins40.05
gbs52
gbs 66

 

Table B

insgbs
37
25
56

 

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

 

disdxnuminshxnumgbs
35122.1252
3515.66
35132.157.

 

 

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

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: 37

Re: Treat Missing values

Hi Keshan,

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 340 views
  • 1 like
  • 3 in conversation