BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ashwini
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

6 REPLIES 6
Ksharp
Super User

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
Calcite | Level 5

Dear Ksharp,

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

Regards,

Ashwini

Ksharp
Super User

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
Calcite | Level 5

Dear Kshap,

Thanks for your kind information.It work .

PeterAndersson
Calcite | Level 5

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
SAS Super FREQ

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3514 views
  • 0 likes
  • 4 in conversation