topic Re: Use SQL to create variables for each class's value in SAS Programming

Use SQL to create variables for each class's value

Lochen — Fri, 01 Sep 2017 23:01:30 GMT

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

Re: Use SQL to create variables for each class's value

novinosrin — Fri, 01 Sep 2017 23:14:25 GMT

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;

Re: Use SQL to create variables for each class's value

ballardw — Fri, 01 Sep 2017 23:15:14 GMT

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.