How do I restructure data?

[Hundredaire]

Have;

X                          Y

Indicator           S

Municipality    K

Gender             T

Value                 0,5

Indicator           S

Municipality    K

Gender             T

Value                 3

Indicator           L

Municipality    K

Gender             M

Value                 10

Want to have;

Indicator           Municipality    Gender             Value                

S                           K                          T                           0,5

S                           K                          T                           3

L                           K                          M                         10

[ballardw]

If that is your entire data set this does basically what is requested. Note the use of a DATA STEP to provide example data that can be used with code.

data have;
  input X :$15. Y :$5.;
datalines;
Indicator           S
Municipality    K
Gender             T
Value                 0,5
Indicator           S
Municipality    K
Gender             T
Value                 3
Indicator           L
Municipality    K
Gender             M
Value                 10
;
Proc sort data=have;
   by x ;
run;

proc transpose data=have out=trans1 (drop=_name_) ;
  by x;
  var y;
run;

proc transpose data=trans1 out=want (drop=_name_);
  id x;
  var col: ;
run;

If you need that Value variable to be numeric you will need another data step to convert the character value to numeric.

---

@Hundredaire wrote:

Have;

X                          Y

Indicator           S

Municipality    K

Gender             T

Value                 0,5

Indicator           S

Municipality    K

Gender             T

Value                 3

Indicator           L

Municipality    K

Gender             M

Value                 10

Want to have;

Indicator           Municipality    Gender             Value                

S                           K                          T                           0,5

S                           K                          T                           3

L                           K                          M                         10

---

[Tom]

You need a third variable to indicate which sets of observations you want to transposed into one.

If they are always in that order then you could use X='Indicator' as the trigger to start a new group.

data step1;
  set have;
  group + (x = 'Indicator');
run;

Now your data is in a format that can be used with PROC TRANSPOSE.

proc transpose data=step1 out=want ( drop=_name_);
  by group;
  id x ;
  var y;
run;

If the variable "VALUE" is supposed to be numeric you will need to convert it later.