SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Convert observations into variables

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Convert observations into variables

Hi,

I am trying to convert one column ob observations into multiple variables, the original data looks something like:

ABCDEFG
XXXXX1X
XXXXX2X
XXXXX3X
.....................
XXXXX99X

and I am trying to convert into something like:

ABCDEF1F2F3...F99
XXXXXGGG...G
XXXXXGGG...G
XXXXXGGG...G

I understand that the proc transpose could achieve such transform, however the problems are:

1. Got ERROR: the ID value "XXX" occurs twice in the input data set. (If use LET, SAS will delete duplicate data which I do not want)

2. In the original data, the value of variable F are integers 1 to 99, however there might not have all these 99 numbers so after the transpose the variables could become something like F30 to F88 (less than 99 F variables).

Hope my description has enough detail and understandable.

Thanks,

Eric


Accepted Solutions
Solution
‎08-16-2012 03:59 PM
Super Contributor
Posts: 1,636

Re: Convert observations into variables

Is the example helpful:

data have;

input id sex $ age;

cards;

1 f 1

1 f 2

1 f 3

1 f 4

1 f 5

1 f 6

2 m 1

2 m 2

2 m 3

;

data want(drop=age);

retain id sex age1-age6;

array _var(*) age1-age6;

set have;

by id;

_var(age)=age;

if last.id then do; output; call missing(of _var(*));end;

proc print;run;

or

data have2;

input id sex $ age weight;

cards;

1 f 1 20

1 f 2 21

1 f 3 22

1 f 4 23

1 f 5 25

1 f 6 26

2 m 1 18

2 m 2 19

2 m 3 40

;

data want2(drop=age weight);

retain id sex age1-age6;

array _var(*) age1-age6;

set have2;

by id;

_var(age)=weight;

if last.id then do; output; call missing(of _var(*));end;

proc print;run;

View solution in original post


All Replies
Solution
‎08-16-2012 03:59 PM
Super Contributor
Posts: 1,636

Re: Convert observations into variables

Is the example helpful:

data have;

input id sex $ age;

cards;

1 f 1

1 f 2

1 f 3

1 f 4

1 f 5

1 f 6

2 m 1

2 m 2

2 m 3

;

data want(drop=age);

retain id sex age1-age6;

array _var(*) age1-age6;

set have;

by id;

_var(age)=age;

if last.id then do; output; call missing(of _var(*));end;

proc print;run;

or

data have2;

input id sex $ age weight;

cards;

1 f 1 20

1 f 2 21

1 f 3 22

1 f 4 23

1 f 5 25

1 f 6 26

2 m 1 18

2 m 2 19

2 m 3 40

;

data want2(drop=age weight);

retain id sex age1-age6;

array _var(*) age1-age6;

set have2;

by id;

_var(age)=weight;

if last.id then do; output; call missing(of _var(*));end;

proc print;run;

PROC Star
Posts: 7,364

Re: Convert observations into variables

I'm not sure I understand what you are trying to do.  It appears like you want to put all up to 99 instances of variable G onto one line.  However, that assumes that variables a thru e have the same values on all records.

It would help if you provided the code that you tried and a bit more of an explanation.

Super User
Posts: 5,093

Re: Convert observations into variables

Subject to all the questions that you have seen about your data and objectives, this might be what you are looking for:

data want;

   array fvalues {99} f1-f99;

   do until (last.e);

        set have;

        by a b c d e;

        fvalues{f} = g;

   end;

   drop f g;

run;

This will not detect how many times the same F appears within a grouping of A/B/C/D/E.  If there are duplicates, it will merely replace the old value with the new.  If you have duplicates, but want to save both of them, the data structure that you described is not capable of holding all the data.

Good luck.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 339 views
  • 0 likes
  • 4 in conversation