DATA Step, Macro, Functions and more

cross table in sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

cross table in sql

I have to create a cross table in proc sql using below mention data set

Studentname  class total

A                     6TH  600

B                     7TH  500

A                     7TH  400

C                      6TH  680

A                      9TH  700

C                      9th    500

I have to create a data set like by proc sql in sas only

Studentname   6th    7th     9th

A                     600   400     700

B                      .       500     .

C                    600      ,.      500

Kindly help me to write a code by proc sql.

Regards,

Ashwini


Accepted Solutions
Solution
‎06-06-2012 03:59 AM
Super User
Posts: 10,023

Re: cross table in sql

Hi. I check it again. Not found a problem.

Maybe you need this:

data have;
input Studentname $ class $ total ;
datalines;
A                     6TH  600
B                     7TH  500
A                     7TH  400
C                      6TH  680
A                      9TH  700
C                      9TH    500
;
run;
proc sql;
create table want as
select studentname,sum(_6TH) as _6TH,sum(_7TH) as _7TH,sum(_9TH) as _9TH
from(select studentname,case when class='6TH' then total else . end as _6TH ,
                    case when class='7TH' then total else . end as _7TH,
                    case when class='9TH' then total else . end as _9TH
                 from have)
 group by studentname;
 quit;

Ksharp

View solution in original post


All Replies
Super User
Posts: 10,023

Re: cross table in sql

OK. No problem.

Assuming there are not duplicated obs for the same student and same class.

data have;
input Studentname $ class $ total ;
datalines;
A                     6TH  600
B                     7TH  500
A                     7TH  400
C                      6TH  680
A                      9TH  700
C                      9TH    500
;
run;
proc sql;
create table want as
select studentname,sum(_6TH) as _6TH,sum(_7TH) as _7TH,sum(_9TH) as _9TH
from(select studentname,case when class='6TH' then total end as _6TH ,
                    case when class='7TH' then total end as _7TH,
                    case when class='9TH' then total end as _9TH
                 from have)
 group by studentname;
 quit;

Ksharp

Frequent Contributor
Posts: 76

Re: cross table in sql

Dear Ksharp,

I am getting syntax error could you please rectifi becouse i fail to do it .Please help me.

Regards,

Ashwini

Solution
‎06-06-2012 03:59 AM
Super User
Posts: 10,023

Re: cross table in sql

Hi. I check it again. Not found a problem.

Maybe you need this:

data have;
input Studentname $ class $ total ;
datalines;
A                     6TH  600
B                     7TH  500
A                     7TH  400
C                      6TH  680
A                      9TH  700
C                      9TH    500
;
run;
proc sql;
create table want as
select studentname,sum(_6TH) as _6TH,sum(_7TH) as _7TH,sum(_9TH) as _9TH
from(select studentname,case when class='6TH' then total else . end as _6TH ,
                    case when class='7TH' then total else . end as _7TH,
                    case when class='9TH' then total else . end as _9TH
                 from have)
 group by studentname;
 quit;

Ksharp

Frequent Contributor
Posts: 76

Re: cross table in sql

Dear Kshap,

Thanks for your kind information.It work .

Occasional Contributor
Posts: 5

Re: cross table in sql

Well you got the answer to the question: How to use PROC SQL on this task, that means in this case using a lot of code including harcoded variables in a case statement.

The answer to your question would be: First ask the proper question. How would I use the SAS system to solve this task?

And the solution is short, simple and flexible:

use PROC TRANSPOSE when you need to restructure a SAS data set

Example  PROC TRANSPOSE code that solves your task:

proc sort data=have out=sorted;

by studentname;

run;

proc transpose data=sorted

out=transposed

name=studentname;

  id class;

  by studentname;

run;

SAS Super FREQ
Posts: 8,864

Re: cross table in sql

Posted in reply to PeterAndersson

Yes, and in addition to PROC TRANSPOSE and the PROC SQL solution, both PROC REPORT and PROC TABULATE create crosstabular reports in the form wanted by the OP.

cynthia

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 446 views
  • 0 likes
  • 4 in conversation