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 |
If i = yes then answer = 1;
Unless there is a variable named yes that contains values in the range 1-10, this statement is nonsense.
@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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.