BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
turcay
Lapis Lazuli | Level 10

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

6 REPLIES 6
MSaber
Calcite | Level 5

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

pivot.png

turcay
Lapis Lazuli | Level 10

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

 



?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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