topic Re: Proc Transpose from Mssql to SAS in SAS Procedures

Proc Transpose from Mssql to SAS

turcay — Sat, 12 Sep 2015 22:10:04 GMT

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;

Re: Proc Transpose from Mssql to SAS

MSaber — Sun, 13 Sep 2015 06:34:18 GMT

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

Re: Proc Transpose from Mssql to SAS

Tom — Sun, 13 Sep 2015 13:20:28 GMT

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

Re: Proc Transpose from Mssql to SAS

turcay — Mon, 14 Sep 2015 18:35:18 GMT

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;

Re: Proc Transpose from Mssql to SAS

Tom — Mon, 14 Sep 2015 19:36:53 GMT

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

Re: Proc Transpose from Mssql to SAS

turcay — Mon, 14 Sep 2015 21:41:10 GMT

Re: Proc Transpose from Mssql to SAS

turcay — Tue, 15 Sep 2015 20:10:09 GMT

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