DATA Step, Macro, Functions and more

Use SQL to create variables for each class's value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Use SQL to create variables for each class's value

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


Accepted Solutions
Solution
‎09-01-2017 07:20 PM
Super User
Posts: 11,343

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

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.

View solution in original post


All Replies
PROC Star
Posts: 283

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

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;

Solution
‎09-01-2017 07:20 PM
Super User
Posts: 11,343

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

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.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 94 views
  • 0 likes
  • 3 in conversation