Sas create table

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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 

 

variableindicatorprice
Apples1400
bananas20.92
bananas30.94
bananas40.96
bananas50.98
bananas61
bananas71.02
bananas81.04
bananas91.06
bananas101.08
bananas111.1
bananas121.12
Orange10.85
Orange20.86
Orange30.87
Orange40.87
Orange50.89
Orange60.9
Orange70.91
Orange80.92
Orange90.93
Orange100.94
Orange110.95
Orange120.96

 

Table B 

 

BananasOrange
79
37
37
23
55
911
910
22
911
412
48
612
25
77
26
49

 

How to get Table C 

 

ApplesBananasOrange
4001.020.93
4000.940.91
4000.940.91
4000.920.87
4000.980.89
4001.060.95
4001.060.94
4000.920.86
4001.060.95
4000.960.96
4000.960.92

 

 


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

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;

View solution in original post


All Replies
Contributor
Posts: 37

Re: Sas create table

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

Super User
Posts: 5,386

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

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: 9,867

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

Re: Sas create table

Hi Xia,

 

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

 

Thanks a lot 

Super User
Posts: 9,867

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;
read all var{Bananas Orange};
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: 37

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: 9,867

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

Re: Sas create table

Hi Keshan,

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

 

Thank you 

Contributor
Posts: 37

Re: Sas create table

Hi Keshan,

 

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

 

Applesb_idxbananaso_idxOrange
40020.9220.86
40020.9230.87
40050.9850.89
40020.9250.89
40020.9260.9
40071.0270.91
40030.9470.91
40030.9470.91
40040.9680.92
40071.0290.93
40040.9690.93
40091.06100.94
40091.06110.95
40091.06110.95
40040.96120.96
40061120.96

 

but i want to be in this order

 

Applesb_idxbananaso_idxOrange
40071.0290.93
40030.9470.91
40030.9470.91
40020.9230.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: 37

Re: Sas create table

Thanks a lot Keshan

Super User
Posts: 9,867

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.

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

Discussion stats
  • 12 replies
  • 596 views
  • 0 likes
  • 3 in conversation