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
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;
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.
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 | . |
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;
Hi Keshan,
Thank you so much for your help . Thanks a lot .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.