Proc Transpose from Mssql to SAS

[turcay]

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;

[MSaber]

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

[Tom]

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

[turcay · Accepted solution]

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;

[Tom]

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,.... ?

[turcay]

[turcay]

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,.... ?

---

?