topic Re: Formatting & rearranging tables using proc sql in Statistical Procedures

Formatting & rearranging tables using proc sql

FerGui1 — Fri, 15 Oct 2021 16:50:27 GMT

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.

Re: Formatting & rearranging tables using proc sql

ballardw — Fri, 15 Oct 2021 17:01:28 GMT

@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.

Re: Formatting & rearranging tables using proc sql

FerGui1 — Fri, 15 Oct 2021 18:19:13 GMT

Yes, L1 and L2 will have numeric values.

Thanks.

Re: Formatting & rearranging tables using proc sql

Patrick — Sat, 16 Oct 2021 02:02:29 GMT

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;