Solved
Contributor
Posts: 40

# Sas create table

I have a Table A and Table B and i am trying to  get table C . Can Anyone Pls help

Table A

 variable indicator price Apples 1 400 bananas 2 0.92 bananas 3 0.94 bananas 4 0.96 bananas 5 0.98 bananas 6 1 bananas 7 1.02 bananas 8 1.04 bananas 9 1.06 bananas 10 1.08 bananas 11 1.1 bananas 12 1.12 Orange 1 0.85 Orange 2 0.86 Orange 3 0.87 Orange 4 0.87 Orange 5 0.89 Orange 6 0.9 Orange 7 0.91 Orange 8 0.92 Orange 9 0.93 Orange 10 0.94 Orange 11 0.95 Orange 12 0.96

Table B

 Bananas Orange 7 9 3 7 3 7 2 3 5 5 9 11 9 10 2 2 9 11 4 12 4 8 6 12 2 5 7 7 2 6 4 9

How to get Table C

 Apples Bananas Orange 400 1.02 0.93 400 0.94 0.91 400 0.94 0.91 400 0.92 0.87 400 0.98 0.89 400 1.06 0.95 400 1.06 0.94 400 0.92 0.86 400 1.06 0.95 400 0.96 0.96 400 0.96 0.92

Accepted Solutions
Solution
‎06-14-2016 11:04 AM
Super User
Posts: 10,784

## Re: Sas create table

Sure .

``````data A;
infile cards expandtabs truncover;
input variable : \$20.	indicator	price;
cards;
Apples	1	400
bananas	2	0.92
bananas	3	0.94
bananas	4	0.96
bananas	5	0.98
bananas	6	1
bananas	7	1.02
bananas	8	1.04
bananas	9	1.06
bananas	10	1.08
bananas	11	1.1
bananas	12	1.12
Orange	1	0.85
Orange	2	0.86
Orange	3	0.87
Orange	4	0.87
Orange	5	0.89
Orange	6	0.9
Orange	7	0.91
Orange	8	0.92
Orange	9	0.93
Orange	10	0.94
Orange	11	0.95
Orange	12	0.96
;
run;
data B;
infile cards expandtabs truncover;
input Bananas	Orange;
cards;
7	9
3	7
3	7
2	3
5	5
9	11
9	10
2	2
9	11
4	12
4	8
6	12
2	5
7	7
2	6
4	9
;
run;

proc sql;
create table want as
select (select price from A  where variable='Apples') as Apples,
a.Bananas as b_idx,b.price as bananas,
a.Orange as o_idx,c.price as Orange
from
B as a
left join
(select * from A where variable='bananas') as b
on a.Bananas=b.indicator
left join
(select * from A where variable='Orange') as c
on a.Orange=c.indicator;
quit;``````

All Replies
Contributor
Posts: 40

## Re: Sas create table

Here The indicator in Table A and is same as the one in table B

Super User
Posts: 5,882

## Re: Sas create table

Neither the input nor the output data makes no sense to me.

Can you in words describe what you want to do, and why?

Data never sleeps
Contributor
Posts: 40

## Re: Sas create table

for example

In Table B for Bananas we have 7 so it checks in Table A for  indicator 7 where column Variable = Bananas  and picks value 1.02 and populates Table C for Bananas as 1.02

Next Table B for Orange 9 it goes and check for Indicator 9 in Table A where Variable = Orange and the value is 0.93 and it picks that value and populates Table C  so it goes on

Pls let me know if you need more and i will give example by selecting more numbers from Table B

Thanks

Super User
Posts: 10,784

## Re: Sas create table

``````data A;
infile cards expandtabs truncover;
input variable : \$20.	indicator	price;
cards;
Apples	1	400
bananas	2	0.92
bananas	3	0.94
bananas	4	0.96
bananas	5	0.98
bananas	6	1
bananas	7	1.02
bananas	8	1.04
bananas	9	1.06
bananas	10	1.08
bananas	11	1.1
bananas	12	1.12
Orange	1	0.85
Orange	2	0.86
Orange	3	0.87
Orange	4	0.87
Orange	5	0.89
Orange	6	0.9
Orange	7	0.91
Orange	8	0.92
Orange	9	0.93
Orange	10	0.94
Orange	11	0.95
Orange	12	0.96
;
run;
data B;
infile cards expandtabs truncover;
input Bananas	Orange;
cards;
7	9
3	7
3	7
2	3
5	5
9	11
9	10
2	2
9	11
4	12
4	8
6	12
2	5
7	7
2	6
4	9
;
run;
data want;
if _n_=1 then do;
set A(obs=1 keep=price rename=(price=Apples));

if 0 then set A(rename=(price=ban));
declare hash h1(dataset:'A(rename=(price=ban) where=(variable="bananas"))');
h1.definekey('indicator');
h1.definedata('ban');
h1.definedone();

if 0 then set A(rename=(price=Ora));
declare hash h2(dataset:'A(rename=(price=Ora) where=(variable="Orange"))');
h2.definekey('indicator');
h2.definedata('Ora');
h2.definedone();
end;
set B;
call missing(ban ,Ora);
rc=h1.find(key:Bananas);
rc=h2.find(key:Orange);
keep Apples ban Ora;
run;``````
Contributor
Posts: 40

## Re: Sas create table

Hi Xia,

Actually the concept is little confusing can u share some information .

Thanks a lot

Super User
Posts: 10,784

## Re: Sas create table

It is a lot more easy for IML.

``````data A;
infile cards expandtabs truncover;
input variable : \$20.	indicator	price;
cards;
Apples	1	400
bananas	2	0.92
bananas	3	0.94
bananas	4	0.96
bananas	5	0.98
bananas	6	1
bananas	7	1.02
bananas	8	1.04
bananas	9	1.06
bananas	10	1.08
bananas	11	1.1
bananas	12	1.12
Orange	1	0.85
Orange	2	0.86
Orange	3	0.87
Orange	4	0.87
Orange	5	0.89
Orange	6	0.9
Orange	7	0.91
Orange	8	0.92
Orange	9	0.93
Orange	10	0.94
Orange	11	0.95
Orange	12	0.96
;
run;
data B;
infile cards expandtabs truncover;
input Bananas	Orange;
cards;
7	9
3	7
3	7
2	3
5	5
9	11
9	10
2	2
9	11
4	12
4	8
6	12
2	5
7	7
2	6
4	9
;
run;

proc iml;
use A;
read all var {price} into A where(variable='Apples');
read all var {price} into B where(variable='bananas');
read all var {price} into O where(variable='Orange');
close;
use B;
close;
x=j(nrow(Bananas),3,.);
x[,1]=A;
x[,2]=B[Bananas-1];
x[,3]=O[Orange];
create want from x[c={Apples bananas Orange}];
append from x;
close;
quit;``````
Contributor
Posts: 40

## Re: Sas create table

Hi Keshan,

I just want to check with you is there any possibility that we can achieve the task using proc transpose or proc sql joins

Can u pls let me know

Thanks

Solution
‎06-14-2016 11:04 AM
Super User
Posts: 10,784

## Re: Sas create table

Sure .

``````data A;
infile cards expandtabs truncover;
input variable : \$20.	indicator	price;
cards;
Apples	1	400
bananas	2	0.92
bananas	3	0.94
bananas	4	0.96
bananas	5	0.98
bananas	6	1
bananas	7	1.02
bananas	8	1.04
bananas	9	1.06
bananas	10	1.08
bananas	11	1.1
bananas	12	1.12
Orange	1	0.85
Orange	2	0.86
Orange	3	0.87
Orange	4	0.87
Orange	5	0.89
Orange	6	0.9
Orange	7	0.91
Orange	8	0.92
Orange	9	0.93
Orange	10	0.94
Orange	11	0.95
Orange	12	0.96
;
run;
data B;
infile cards expandtabs truncover;
input Bananas	Orange;
cards;
7	9
3	7
3	7
2	3
5	5
9	11
9	10
2	2
9	11
4	12
4	8
6	12
2	5
7	7
2	6
4	9
;
run;

proc sql;
create table want as
select (select price from A  where variable='Apples') as Apples,
a.Bananas as b_idx,b.price as bananas,
a.Orange as o_idx,c.price as Orange
from
B as a
left join
(select * from A where variable='bananas') as b
on a.Bananas=b.indicator
left join
(select * from A where variable='Orange') as c
on a.Orange=c.indicator;
quit;``````
Contributor
Posts: 40

## Re: Sas create table

Hi Keshan,

Thank you . I will run the code and will let u know

Thank you

Contributor
Posts: 40

## Re: Sas create table

Hi Keshan,

I am able to achieve task but the end output i am getting is

 Apples b_idx bananas o_idx Orange 400 2 0.92 2 0.86 400 2 0.92 3 0.87 400 5 0.98 5 0.89 400 2 0.92 5 0.89 400 2 0.92 6 0.9 400 7 1.02 7 0.91 400 3 0.94 7 0.91 400 3 0.94 7 0.91 400 4 0.96 8 0.92 400 7 1.02 9 0.93 400 4 0.96 9 0.93 400 9 1.06 10 0.94 400 9 1.06 11 0.95 400 9 1.06 11 0.95 400 4 0.96 12 0.96 400 6 1 12 0.96

but i want to be in this order

 Apples b_idx bananas o_idx Orange 400 7 1.02 9 0.93 400 3 0.94 7 0.91 400 3 0.94 7 0.91 400 2 0.92 3 0.87

is that something i can get it i want the order to be same as in B table . Can u pls help

Thanks

Contributor
Posts: 40

## Re: Sas create table

Thanks a lot Keshan

Super User
Posts: 10,784

## Re: Sas create table

Make an ORDER variable:

``````data A;
infile cards expandtabs truncover;
input variable : \$20.	indicator	price;
cards;
Apples	1	400
bananas	2	0.92
bananas	3	0.94
bananas	4	0.96
bananas	5	0.98
bananas	6	1
bananas	7	1.02
bananas	8	1.04
bananas	9	1.06
bananas	10	1.08
bananas	11	1.1
bananas	12	1.12
Orange	1	0.85
Orange	2	0.86
Orange	3	0.87
Orange	4	0.87
Orange	5	0.89
Orange	6	0.9
Orange	7	0.91
Orange	8	0.92
Orange	9	0.93
Orange	10	0.94
Orange	11	0.95
Orange	12	0.96
;
run;
data B;
infile cards expandtabs truncover;
input Bananas	Orange;
order+1;
cards;
7	9
3	7
3	7
2	3
5	5
9	11
9	10
2	2
9	11
4	12
4	8
6	12
2	5
7	7
2	6
4	9
;
run;

proc sql;
create table want as
select a.order,(select price from A  where variable='Apples') as Apples,
a.Bananas as b_idx,b.price as bananas,
a.Orange as o_idx,c.price as Orange
from
B as a
left join
(select * from A where variable='bananas') as b
on a.Bananas=b.indicator
left join
(select * from A where variable='Orange') as c
on a.Orange=c.indicator
order by a.order;
quit;``````
🔒 This topic is solved and locked.