topic Re: excel import in SAS Programming

excel import

elsalam — Fri, 11 May 2018 17:55:16 GMT

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

Answer

Q10

Answer

Cary

111

yes

Raleigh

112

yes

Michigan

115

yes

South Carolina

119

yes

Re: excel import

Kurt_Bremser — Fri, 11 May 2018 18:04:04 GMT

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.

Re: excel import

ballardw — Fri, 11 May 2018 19:58:15 GMT

@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,

/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;