topic Re: cross table in sql in SAS Programming

cross table in sql

Ashwini — Wed, 06 Jun 2012 02:44:43 GMT

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

Re: cross table in sql

Ksharp — Wed, 06 Jun 2012 04:24:12 GMT

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

Re: cross table in sql

Ashwini — Wed, 06 Jun 2012 07:32:21 GMT

Dear Ksharp,

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

Regards,

Ashwini

Re: cross table in sql

Ksharp — Wed, 06 Jun 2012 07:59:22 GMT

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

Re: cross table in sql

Ashwini — Wed, 06 Jun 2012 17:57:44 GMT

Dear Kshap,

Thanks for your kind information.It work .

Re: cross table in sql

PeterAndersson — Sun, 10 Jun 2012 21:18:43 GMT

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;

Re: cross table in sql

Cynthia_sas — Mon, 11 Jun 2012 17:25:22 GMT

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