Transposing data with empty fields.

Traian — Tue, 30 Nov 2021 21:56:42 GMT

I'm trying to transpose a dataset in the following way.

Input:

id var item value

2 q1 . 1

4 q2 2 3

7 q2 5 3

8 q2 6 4

10 q3 1 2

23 q4 . 4

24 r1 . 5

Desired output:

id q1 q2_2 q2_5 q2_6 q3_1 q4 r1

2 1 . . . . . .

4 . 3 . . . . .

7 . . 3 . . . .

8 . . . 4 . . .

10 . . . . 2 . .

23 . . . . . 4 .

24 . . . . . . 5

I tried this approach.

data have;
input id    var $    item    value;
cards;
2 q1 . 1
4 q2 2 3
7 q2 5 3
8 q2 6 4
10 q3 1 2
23 q4 . 4
24 r1 . 5
;
run;


PROC SORT data = have;
BY id var;
run; 

PROC TRANSPOSE data = have out = want delimiter=_;
by id;
id var item;
var value;
run;

The issue i have here is that i cannot transpose the variables where ITEM is an empty variable. Compared to the desired output i receive a table without q1, q4 and r1. How can i deal with the empty variables when transposing?

Re: Transposing data with empty fields.

andreas_lds — Wed, 01 Dec 2021 07:57:26 GMT

Don't know what you want to do with the transposed format, but here you go:

data pre_want;
   set have;
   length key $ 4; /* needs adjustment */
   key = ifc(missing(item), ' ', put(item, 1.));
   key = catx('_', var, key);
   drop var item;
run;

proc transpose data=pre_want out=want;
   by key;
   id id;
   var value;
run;

You need to concatenate "var" and "item" before proc transpose.

topic Transposing data with empty fields. in SAS Programming

Transposing data with empty fields.

Re: Transposing data with empty fields.