Developer Forum

Hi All,

I need to create a report that has a table with columns. At the top of each page is the person's name. The table has 8 columns. The name of the subscale is at the top (right above the table) of each table to the far left. The first cell (A1) in the first column has the text "My Principal...", then right underneath that in cell A2 the items begin. Item 1 says, "exhibits an understanding..." Cell B1 has the text "Strongly Agree", cell C1 "Agree", cell D1 "Disagree', cell E1 "Strongly Disagree", cell F1 "Not Applicable". Item 2 begins in cell A3. Percentages are in cells B2, C2, D2, E2, and F2 for item 1. The table is the same for the other items that fall under that subscale. There are other tables just like what I described for the remaining 9 subscales. The last page of the report has a table that compares the assistant principal's percentages on each of the competencies to All school Principals in that school region and to All Principals in the entire school district. A!
region is comprised of a number of schools. Our school system has 8 regions.

I have one text file per school. I was told that I need to first transpose the data before using proc tabulate or proc report. I am fairly new at this and am having a hard time with proc transpose.

I have copied the first line of a text file below so you can refer to it.

N 405001
ABBBBBBBAABBBBABBBAABBBBBBAAAABBAAAAAAAABBBBBBBBBAAAAAAAAAAAABBBBBBBBBBBBBBBBB


The data doesn't start until after the N. After the N, the first 3 digits are the school code followed by 001 or 002 indicating whether or not the respondent is classified staff or non-classified staff.

I know I need to use either proc tabulate or proc report or both to come up with the finished product which would be tables in Word that summarizes the data.


If anyone can help me, I would really appreciate it. I am a novice at this and need help getting started.

Thanks so much,

Carol

o SAS-L:

I normally wouldn't do this, but I'm not sufficiently familiar with either
tabulate or report and Carol sent me her data and desired output off line.

If I had to do what she needs to do, and none of my staff were available,
I'd simply run the following proc freqs and do a bunch of copying and
pasting.

However, as I'm sure there are far more efficient methods available, I'll
leave it to the rest of you to suggest some possibilities:

/* Carol:*/
/*filename io 'c:\__p405.yyy';*/
/*data P405;*/
/* infile io missover;*/
/* input @41 code 3. @46 Job 1. @52 (q1-q78) ($1.);*/
/*run;*/

/* but first fix code in the last record in your file*/
/* as it contains*/
/* *05001 rather than the 405001*/

data P405;
infile cards missover;
input @41 code 3. @46 Job 1. @52 (q1-q3) ($1.);
label q1="Exhibits an understanding of the taught and tested curriculum";
label q2="Supports opportunities for teachers to develop in-depth
knowledge of their content";
label q3="Monitors the alignment of curriculum, assessment, and
instruction";
cards;
501000001001031009001 5322 #0001 N 405001 ABB
501000002001031009001 5322 #0001 N 405001 BBB
501000003001031009001 5322 #0001 N 405001 BBB
501000004001031009001 5322 #0001 N 405002 AAA
501000005001031009001 5322 #0001 N 405001 AAA
501000006001031009001 5322 #0001 N 405001 AAA
501000007001031009001 5322 #0001 N 405002 BBB
501000008001031009001 5322 #0001 N 405002 EEE
501000009001031009001 5322 #0001 N 405001 CAA
501000010001031009001 5322 #0001 N 405002 AAA
;

proc format;
value $qfmt A='SA' B='A' C='D' D='SD' E='NA';
value $qfmta A='A' B='A' C='D' D='SD' E='NA';
run;

*My brute force, followed by copy/paste method;

proc sort data = P405;
by Job;
run;

proc freq data=p405;
tables q1-q3;
format q1-q3 $qfmt.;
by job;
run;

proc freq data=p405;
tables q1-q3;
format q1-q3 $qfmta.;
by job;
run;

proc freq data=p405;
tables q1-q3;
format q1-q3 $qfmta.;
run;

/*
what she wants (I think) although its really for 78 items rather than the
3 shown in the example:

Job 1
Strongly Dis- Strongly Not Ap-
Agree Agree Agree Disagree plicable Job1 All Jobs
----------Items----------- (SA) (A) (D) (SD) (NA) SA+A SA+A
Exhibits an understanding 50% 33% 17% 0% 0% 83% 80%
of the taught and tested
curriculum

Supports opportunities for 50% 50% 0% 0% 0% 100% 90%
teachers to develop
in-depth knowledge of
their content

Monitors the alignment of 50% 50% 0% 0% 0% 100% 90%
curriculum, assessment,
and instruction
<New Page)------------------------------------------
Strongly Dis- Strongly Not Ap-
Agree Agree Agree Disagree plicable Job1 All Jobs
----------Items----------- (SA) (A) (D) (SD) (NA) SA+A SA+A
Exhibits an understanding 50% 25% 0% 0% 25% 75% 80%
of the taught and tested
curriculum

Supports opportunities for 50% 25% 0% 0% 25% 75% 90%
teachers to develop
in-depth knowledge of
their content

Monitors the alignment of 50% 25% 0% 0% 25% 75% 90%
curriculum, assessment,
and instruction

*/
--------
On Wed, 14

i Arthur,

Thanks for your input. I can use your code to help me get started. The way you have constructed the table is exactly what mine needs to look like. It just has to be in a nice report quality format. That's why I was wanting to go with proc tabulate and proc report.

Can anyone help me with how to do this in proc tabulate and proc report?

Carol

To SAS-L:

I normally wouldn't do this, but I'm not sufficiently familiar with either
tabulate or report and Carol sent me her data and desired output off line.

If I had to do what she needs to do, and none of my staff were available,
I'd simply run the following proc freqs and do a bunch of copying and
pasting.

However, as I'm sure there are far more efficient methods available, I'll
leave it to the rest of you to suggest some possibilities:

/* Carol:*/
/*filename io 'c:\__p405.yyy';*/
/*data P405;*/
/* infile io missover;*/
/* input @41 code 3. @46 Job 1. @52 (q1-q78) ($1.);*/
/*run;*/

/* but first fix code in the last record in your file*/
/* as it contains*/
/* *05001 rather than the 405001*/

data P405;
infile cards missover;
input @41 code 3. @46 Job 1. @52 (q1-q3) ($1.);
label q1="Exhibits an understanding of the taught and tested curriculum";
label q2="Supports opportunities for teachers to develop in-depth
knowledge of their content";
label q3="Monitors the alignment of curriculum, assessment, and
instruction";
cards;
501000001001031009001 5322 #0001 N 405001 ABB
501000002001031009001 5322 #0001 N 405001 BBB
501000003001031009001 5322 #0001 N 405001 BBB
501000004001031009001 5322 #0001 N 405002 AAA
501000005001031009001 5322 #0001 N 405001 AAA
501000006001031009001 5322 #0001 N 405001 AAA
501000007001031009001 5322 #0001 N 405002 BBB
501000008001031009001 5322 #0001 N 405002 EEE
501000009001031009001 5322 #0001 N 405001 CAA
501000010001031009001 5322 #0001 N 405002 AAA
;

proc format;
value $qfmt A='SA' B='A' C='D' D='SD' E='NA';
value $qfmta A='A' B='A' C='D' D='SD' E='NA';
run;

*My brute force, followed by copy/paste method;

proc sort data = P405;
by Job;
run;

proc freq data=p405;
tables q1-q3;
format q1-q3 $qfmt.;
by job;
run;

proc freq data=p405;
tables q1-q3;
format q1-q3 $qfmta.;
by job;
run;

proc freq data=p405;
tables q1-q3;
format q1-q3 $qfmta.;
run;

/*
what she wants (I think) although its really for 78 items rather than the
3 shown in the example:

Job 1
Strongly Dis- Strongly Not Ap-
Agree Agree Agree Disagree plicable Job1 All Jobs
----------Items----------- (SA) (A) (D) (SD) (NA) SA+A SA+A
Exhibits an understanding 50% 33% 17% 0% 0% 83% 80%
of the taught and tested
curriculum

Supports opportunities for 50% 50% 0% 0% 0% 100% 90%
teachers to develop
in-depth knowledge of
their content

Monitors the alignment of 50% 50% 0% 0% 0% 100% 90%
curriculum, assessment,
and instruction
<New Page)------------------------------------------
Strongly Dis- Strongly Not Ap-
Agree Agree Agree Disagree plicable Job1 All Jobs
----------Items----------- (SA) (A) (D) (SD) (NA) SA+A SA+A
Exhibits an understanding 50%