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
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
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
Dear Ksharp,
I am getting syntax error could you please rectifi becouse i fail to do it .Please help me.
Regards,
Ashwini
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
Dear Kshap,
Thanks for your kind information.It work .
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.