@elsalam wrote:
I tried to read the data from excel but not working with the following data.
I was unable to upload the spreadsheet here as well so that I do copy and paste.
I need to make a spreadsheet to sum the answer. Therefore, the first step should read "yes' as 1 at the answer column.
ROC IMPORT DATAFILE="/home/coccus030/sasuser.v94/April 2.xlsx";
OUT=work.SpotAudit
DBMS=XLSX
REPLACE;
RUN;
Data SpotAudit2;
set work.SpotAudit;
array QC(10) Q1 -- Q10;
Do i = 1 to 10;
If i = yes then answer = 1;
else answer = 0;
end;
/** Print the results. **/
PROC PRINT DATA=work.SpotAudit; RUN;
| Center |
DMS |
Q1 |
Answer |
Q2 |
Answer |
Q3 |
Answer |
Q4 |
Answer |
Q5 |
Answer |
Q6 |
Answer |
Q7 |
Answer |
Q8 |
Answer |
Q9 |
Answer |
Q10 |
Answer |
| Cary |
111 |
yes |
|
yes |
|
yes |
|
yes |
|
no |
|
no |
|
yes |
|
yes |
|
yes |
|
yes |
|
| Raleigh |
112 |
yes |
|
yes |
|
no |
|
yes |
|
yes |
|
no |
|
yes |
|
yes |
|
yes |
|
yes |
|
| Michigan |
115 |
no |
|
no |
|
no |
|
yes |
|
no |
|
no |
|
yes |
|
no |
|
no |
|
no |
|
| South Carolina |
119 |
no |
|
yes |
|
no |
|
yes |
|
no |
|
no |
|
yes |
|
no |
|
no |
|
no |
|
What makes you think you need a spreadsheet to sum anything??
Also is that data supposed to be 1) your spreadsheet you read in 2) your SAS data set after importing the data 3) the results of the proc print?
I am wondering why you have
PROC PRINT DATA=work.SpotAudit; RUN; which is the data before you try to add your "answer" values.
I would bet a short stack of nickels that if you run this code:
PROC PRINT DATA=work.SpotAudit2; RUN;
That you will see a variable at the right of the output named Yes with all the values a missing.
For additional education you might try this code as well:
Proc tabulate data=work.SpotAudit;
class Q1 - Q10;
table Q1 - Q10,
n
/misstext='0';
run;
which will give a count of the yes and no answers.
I think you intended something more like this data step because you wanted 10 separate answers. Other wise you only have the result in Answer of the Q10 value.
Data SpotAudit2;
set work.SpotAudit;
array QC(10) Q1 -- Q10;
array Answer(10);
Do i = 1 to 10;
If qc[i] = 'yes' then answer[i] = 1;
else answer[i] = 0;
end;
end;
proc print data=work.spotaudit2 noobs;
sum answer1-answer10;
run;
Click image to register for webinar
