cross table in sql

[Ashwini]

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

[Ksharp]

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

[Ashwini]

Dear Ksharp,

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

Regards,

Ashwini

[Ksharp · Accepted solution]

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

[Ashwini]

Dear Kshap,

Thanks for your kind information.It work .

[PeterAndersson]

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;

[Cynthia_sas]

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