Formatting & rearranging tables using proc sql

[FerGui1]

Hello,

I have two data sets, data1 & data2, shown below

data1
label	name	var	value
L1	A	v1	4
L1	B	v1	3
L1	C	v1	6
L1	D	v1	7
L1	E	v1	4
L2	A	v1	5
L2	B	v1	1
L2	C	v1	2
L2	D	v1	8
L2	E	v1	3
L1	A	v2	18
L1	B	v2	12
L1	C	v2	4
L1	D	v2	18
L1	E	v2	14
L2	A	v2	12
L2	B	v2	4
L2	C	v2	12
L2	D	v2	20
L2	E	v2	10

data2
label	var	stat
L1	v1	0.05
L1	v2	0.01
L2	v1	0.07
L2	v2	0.05

I want to use them to create a new table, table1, with the format below:

v1
name	L1	L2
A	4	5
B	3	1
C	6	2
D	7	8
E	4	3
stat	0.05	0.07
v2
name	L1	L2
A	18	12
B	12	4
C	4	12
D	18	20
E	14	10
stat	0.01	0.05

I do not know much about the use of proc sql, but for wat I read it seems that it is the right tool for doing this.

Any help on this will be much appreciated.

[ballardw]

---

@FerGui1 wrote:

Hello,

I have two data sets, data1 & data2, shown below

data1
label	name	var	value
L1	A	v1	4
L1	B	v1	3
L1	C	v1	6
L1	D	v1	7
L1	E	v1	4
L2	A	v1	5
L2	B	v1	1
L2	C	v1	2
L2	D	v1	8
L2	E	v1	3
L1	A	v2	18
L1	B	v2	12
L1	C	v2	4
L1	D	v2	18
L1	E	v2	14
L2	A	v2	12
L2	B	v2	4
L2	C	v2	12
L2	D	v2	20
L2	E	v2	10

data2
label	var	stat
L1	v1	0.05
L1	v2	0.01
L2	v1	0.07
L2	v2	0.05

I want to use them to create a new table, table1, with the format below:

v1
name	L1	L2
A	4	5
B	3	1
C	6	2
D	7	8
E	4	3
stat	0.05	0.07
v2
name	L1	L2
A	18	12
B	12	4
C	4	12
D	18	20
E	14	10
stat	0.01	0.05

I do not know much about the use of proc sql, but for wat I read it seems that it is the right tool for doing this.

Any help on this will be much appreciated.

---

And what will you do with that data set? Do you expect that L1 and L2 will actually have numeric values?

This is more the the realm of a report of some sort, not a data set. SQL will likely not be the best tool to approach the report either.

SQL may joint data sets so the observations in the data set contain the needed elements but that appearance is not what it is designed for.

[FerGui1]

Yes, L1 and L2  will have numeric values.

Thanks.

[Patrick]

I believe a SQL would become rather cumbersome. Below one way to go.

data have1;
  input label $ name $  var $ value;
  datalines;
L1 A v1 4
L1 B v1 3
L1 C v1 6
L1 D v1 7
L1 E v1 4
L2 A v1 5
L2 B v1 1
L2 C v1 2
L2 D v1 8
L2 E v1 3
L1 A v2 18
L1 B v2 12
L1 C v2 4
L1 D v2 18
L1 E v2 14
L2 A v2 12
L2 B v2 4
L2 C v2 12
L2 D v2 20
L2 E v2 10
;

data have2;
  input label $ var $ stat;
  datalines;
L1 v1 0.05
L1 v2 0.01
L2 v1 0.07
L2 v2 0.05
;

proc sort data=have1;
  by var name;
run;

proc transpose data=have1 out=have1_trans(drop=_:) ;
  by var name;
  id label;
  var value;
run;

proc sort data=have2;
  by var;
run;
proc transpose data=have2 out=have2_trans(drop=_:) ;
  by var;
  id label;
  var stat;
run;

data want;
  set have1_trans have2_trans(in=in2);
  by var;
  rename var=version;
  if in2 then name='stat';
run;