Help using Base SAS procedures

Proc Transpose from Mssql to SAS

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

Proc Transpose from Mssql to SAS

[ Edited ]

Hello,

I have a mssql code's below which i'm trying to write to SAS programming language.I am a little bir confused. Could you help me, please ?

 

begin tran
SELECT *
INTO newtable
FROM maintable
PIVOT
(
SUM(result)
FOR resul_id
IN ([847],[848],[849],[850],[851],[852],[853],[854],[855],[856],[857],[858],[859],[860],[861],[862],[863],[864],[865],[866],[867],[868],[869],[870],[871],[872],[873],[874],[875],[876],[877],[878],[879],[880],[881],[882],[883],[884],[885],[886],[887],[888],[889],[890],[891],[892],[893],[894],[895])
) PivotTable
commit tran

 

SAS Code ->

proc transpose data=maintable
out=newtable
Name=Pivot;
run;


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super Contributor
Posts: 381

Re: Proc Transpose from Mssql to SAS

[ Edited ]

I solved the problem. It wants three steps.

The data which i tried to transpose to pivot is below.

1 323 AAA
2 432 AAA
3 654 ABA
4 21 AAA
5 23 ABA
1 56 AAA
2 22 ABA
3 31 AAA
5 87 AAA
3 43 ABA
2 11 ABA
1 323 AAA

And this is the solution.

 

proc sql;
create table work.summary1 as
select id,deger2,sum(column) as deger from work.sqltosas
group by id,column2;
quit;
proc sort data=work.summary1
out=work.sorted;
by column2;
run;
proc transpose data=work.sorted
out=work.yenisas(drop=_: ) let;

var column;
by column2;
id id;
run;

View solution in original post


All Replies
Occasional Contributor
Posts: 9

Re: Proc Transpose from Mssql to SAS

Dear,

 

in Proc Transpose you can't do Sum and apply in() function. you have to make your MSSQL query in two queries in SAS (Proc Transpose and Proc SQL).

 

if you need to make both in one step then you have to go through Data Step.

 

let me know if you need any further clarifications

Super User
Super User
Posts: 6,500

Re: Proc Transpose from Mssql to SAS

What are your inputs and outputs?  Perhaps PROC SUMMARY is what you want.  If you are subsetting then you probably want a WHERE clause.

Solution
‎09-25-2015 06:23 AM
Super Contributor
Posts: 381

Re: Proc Transpose from Mssql to SAS

[ Edited ]

I solved the problem. It wants three steps.

The data which i tried to transpose to pivot is below.

1 323 AAA
2 432 AAA
3 654 ABA
4 21 AAA
5 23 ABA
1 56 AAA
2 22 ABA
3 31 AAA
5 87 AAA
3 43 ABA
2 11 ABA
1 323 AAA

And this is the solution.

 

proc sql;
create table work.summary1 as
select id,deger2,sum(column) as deger from work.sqltosas
group by id,column2;
quit;
proc sort data=work.summary1
out=work.sorted;
by column2;
run;
proc transpose data=work.sorted
out=work.yenisas(drop=_: ) let;

var column;
by column2;
id id;
run;

Super User
Super User
Posts: 6,500

Re: Proc Transpose from Mssql to SAS

[ Edited ]

You posted three columns of data, but no column names. Your SQL references 4 varaibles: ID DEGER2 COLUMN and COLUMN2.

What does your output look like?  Do have variables named AAA and ABA?  Do you have a observations for ID=1,2,3,4,5? Or do you have observations for ID=11,21,22,23,.... ?

 

Super Contributor
Posts: 381

Re: Proc Transpose from Mssql to SAS

pivot.png

Super Contributor
Posts: 381

Re: Proc Transpose from Mssql to SAS

Are there any simple methods to make this


Tom wrote:

You posted three columns of data, but no column names. Your SQL references 4 varaibles: ID DEGER2 COLUMN and COLUMN2.

What does your output look like?  Do have variables named AAA and ABA?  Do you have a observations for ID=1,2,3,4,5? Or do you have observations for ID=11,21,22,23,.... ?

 



?

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 383 views
  • 0 likes
  • 3 in conversation