@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; ... View more

Do not expect text data pasted into a message window on this forum to match your actual text data as the software reformats it. Better is to paste text data into a code box opened using the forums {I} menu icon.   I suspect that your actual data has TAB characters in it. Therefor the YEAR is likely not actually in columns 7-12. consistently When I pasted your example data into in a text editor I get this: icc-M 1970 music: 03/14/2017 ipp-e 1989 music: 03/19/2018 iff-e 1880 music: 03/15/2017 ioo-p 1996 music: 03/10/2015 odd-w 2001 music: 03/16/2011 odd-p 1965 music: 03/01/2011 egg-o 2000 music: 03/17/2010 eee-w 1947 music: 03/04/2016 which shows the year appearing in different columns. Which will happen with tab replacements.   It might help to actually attach the data file as a TXT file so we can verify the actual contents.   BTW That does NOT qualify as messy data by a long shot. But it will require understanding of what happens when you read from 1) a fixed column that does not align with your actual data, 2) the actual location of where the column pointer when reading using the @'music:' column pointer and how it interacts with a format specified on the input statement.   You should also post in a code box the very likely invalid data messages with your code from the log. ... View more

Saying "libname and infile not work." - is not enough. To help you we need the full log. ... View more

@elsalam wrote: Yes, I changed it to if team in ('Campbell','Batter') then Conference = 'AFC-NORTH'; else Conference = 'AFC-WEST';   It still the entire column are AFC-WEST.   And, if I have more than two value to be matched.   Is it a faster way to match them? Show the entire data step and any messages from log. Paste into a code box opened using the forum {I} icon to preserver formatting of code and any error or warning messages. It might also help to provide some actual values from you data as a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.   Note that the comparison matches the entire field. So if the value has any leading blanks or other characters than "Campbell" the match will return false or if the data is not in the same case, "campbell" is not the same as "CAMPBELL" or "Campbell" or "cAmpbell"   The list of values used in the in comparison can be more than two, I just didn't feel like typing more. ... View more

You are using & within the INPUT statement, without understanding what it means.  Take a look, this should clear it up:   http://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n0lrz3gb7m9e4rn137op544ddg0v.htm&docsetVersion=9.4&locale=en     ... View more

Homework? If you need help you need to provide more information, specifically, what are you trying to do, what do you have and what have you tried so far.      @elsalam wrote: Three questions:   1. convert all consonants in the values for country and state variable to uppercase all vowels to lowercase   2. use the variable super to indicate winner, runner up, and non - contenders notes:  winner for river, runner up for fox, non contenders for other teams   3. assign variable conference for each team: Bills belong to conference South River belong to conference SW   Thx           ... View more