Help using Base SAS procedures

SAS QUERY EQUIVALENT TO SQL SERVER PIVOT

Accepted Solution Solved
Reply
Occasional Learner
Posts: 1
Accepted Solution

SAS QUERY EQUIVALENT TO SQL SERVER PIVOT

I have already created a table and the data looks like:

product    col2          price

A              456          5666

A              123          4000

B              333           2569

B              456          1500

 Now, I want to transpose the result set in the following way:

col2    A           B

456    5666     1500

123    4000     null

333   null        2569

 

This can be done easily in sql server using PIVOT function. I was trying to use PROC TRANSPOSE in SAS but could not get the proper result. 

 


Accepted Solutions
Solution
‎01-30-2017 07:36 AM
Super User
Super User
Posts: 7,942

Re: SAS QUERY EQUIVALENT TO SQL SERVER PIVOT

NOTE: Post test data in the form of a datastep.

This should be simple in proc transpose, what have you tried?

proc sort data=have;
  by col2 col1;
run;
proc transpose data=have out=want;
  by col2;
  var price;
  id col1;
  idlabel col1;
run;

View solution in original post


All Replies
Solution
‎01-30-2017 07:36 AM
Super User
Super User
Posts: 7,942

Re: SAS QUERY EQUIVALENT TO SQL SERVER PIVOT

NOTE: Post test data in the form of a datastep.

This should be simple in proc transpose, what have you tried?

proc sort data=have;
  by col2 col1;
run;
proc transpose data=have out=want;
  by col2;
  var price;
  id col1;
  idlabel col1;
run;
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 827 views
  • 1 like
  • 2 in conversation