topic Re: How do I restructure data? in SAS Programming

How do I restructure data?

Hundredaire — Thu, 02 Jun 2022 11:42:05 GMT

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

Re: How do I restructure data?

ballardw — Thu, 02 Jun 2022 17:46:19 GMT

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

Re: How do I restructure data?

Tom — Thu, 02 Jun 2022 17:58:57 GMT

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.