DATA Step, Macro, Functions and more

excel import

Reply
Contributor
Posts: 20

excel import

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;

 

CenterDMSQ1AnswerQ2AnswerQ3AnswerQ4AnswerQ5AnswerQ6AnswerQ7AnswerQ8AnswerQ9AnswerQ10Answer
Cary111yes yes yes yes no no yes yes yes yes 
Raleigh112yes yes no yes yes no yes yes yes yes 
Michigan115no no no yes no no yes no no no 
South Carolina119no yes no yes no no yes no no no 
Super User
Posts: 9,870

Re: excel import

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,293

Re: excel import


@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;
Ask a Question
Discussion stats
  • 2 replies
  • 94 views
  • 0 likes
  • 3 in conversation