Use SQL to create variables for each class's value

[Lochen]

Hi everyone,

I am new to SAS and SQL. I have a dataset with ID, class and the related values. It's like,

ID       CLASS            VALUE

1             A                     5

1             B                     7

1             C                    10

2             A                     7

2             B                     5

2             C                    12

Can anyone help to use SQL to create 3 variables A, B , C to store different class values for each ID

The expected output table looks like

ID    A    B    C

1     5     7    10

2     7     5     12

[novinosrin]

why not accomplish your req with a super simple proc transpose-

data have;

input  ID       CLASS $          VALUE;

datalines;

1             A                     5

1             B                     7

1             C                    10

2             A                     7

2             B                     5

2             C                    12

;

proc transpose data=have out=want(drop=_name_);

by id;

id class;

var value;

run;

[ballardw · Accepted solution]

Like this?

data have;
   input ID       CLASS $            VALUE;
datalines;
1             A                     5
1             B                     7
1             C                    10
2             A                     7
2             B                     5
2             C                    12
;
run;

proc transpose data=have out=want  (drop=_name_)
     let;
   by ID;
   id Class;
   var value;
run;

The data set is to have a concrete example and is the perferred way to show your data.

Proc Transpose is designed to do basic tranpostions from long to wide or wide to long data structures.

Note that ID used on the BY statement is your identification variable.

ID as used in ID Class; is the instruction that says "use the value of the variable class to create the identification (name) of the values.