Michelle, This solution is from a different angle. I'm assuming the sample data you have included is your answer "key". If you don't already have your answer "key" already in a SAS data set, the first data step will read it into one, but using all character variables. The second data step will then produce an external file with the actual SAS code that you had been cutting and pasting before.

data sample; input origid$ newid$ avalue$ bvalue$ cvalue$; cards; Q1 N1 1 2 3 Q2 N2 3 1 2 Q3 N3 2 1 3 Q4 N4 1 2 3 ;

data _null_; file "C:\Newcode.sas" ; set sample; put "IF " origid " = 'A' THEN " newid " = " avalue ";"; put "ELSE IF " origid " = 'B' THEN " newid " = " bvalue ";"; put "ELSE IF " origid " = 'C' THEN " newid " = " cvalue ";"; run;

Then, the contents of Newcode.sas will look like this:

IF Q1 = 'A' THEN N1 = 1 ; ELSE IF Q1 = 'B' THEN N1 = 2 ; ELSE IF Q1 = 'C' THEN N1 = 3 ; IF Q2 = 'A' THEN N2 = 3 ; ELSE IF Q2 = 'B' THEN N2 = 1 ; ELSE IF Q2 = 'C' THEN N2 = 2 ; IF Q3 = 'A' THEN N3 = 2 ; ELSE IF Q3 = 'B' THEN N3 = 1 ; ELSE IF Q3 = 'C' THEN N3 = 3 ; IF Q4 = 'A' THEN N4 = 1 ; ELSE IF Q4 = 'B' THEN N4 = 2 ; ELSE IF Q4 = 'C' THEN N4 = 3 ;

You can then copy and paste this code wherever you need it. Yes, this is an extra step, but this eliminates the need to code a specific format for each question (since that is already done in the sample data set).

-Bob

-----Original Message----- From: Michelle Sullivan [mailto:michelle@UNREALITIES.COM] Sent: Wednesday, February 06, 2002 11:09 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Survey Question Scoring Techniques

I have recently joined a company where I have access to SAS, so I am happily working my way through problems on the job as a way to teach myself this new-to-me language.

Yesterday, I was tasked with translating survey data answers (values= A B C) into numerical values, which were variable by question number. I solved the problem with some rather laborious cutting & pasting and, all the while, I was sure there must be a better way to accomplish this in SAS. Please let me know if you know of a cleaner way to solve this problem.

Here is a more detailed description of the problem & my less-than-optimal solution:

Problem:

I was asked to set up a way to create a "new" list of observations with numerical values (based on the table below) from the existing list of observations. note: the ellipses (...) designate column break

Column headers are: Orig OBS ID ... New OBS ID ... Replacement Value for 'A' Answer ... Replacement Value for 'B' Answer ... Replacement Value for 'C' Answer

Sample Data: Q1 ... N1 ... 1 ... 2 ... 3 Q2 ... N2 ... 3 ... 1 ... 2 Q3 ... N3 ... 2 ... 1 ... 3 Q4 ... N4 ... 1 ... 2 ... 3 ...and so on ...

So, as you can see, each line is unique in that it has a different ID number and different value assigned to the answer indicators 'A' 'B' and 'C'

My solution:

To cut and paste the following line

IF Q1='A' THEN N1=1; ELSE IF Q1='B' THEN N1=2; ELSE IF Q1='C' THEN N1=3;

to create a template for the next line. Then I did a search and replace to change all of the Qs and Ns to reflect the next observation (in this case, Q1 changes to Q2, N1 changes to N2) and then I hand-changed all of the values according to the table. The final product looked something like this:

IF Q1='A' THEN N1=1; ELSE IF Q1='B' THEN N1=2; ELSE IF Q1='C' THEN N1=3; IF Q2='A' THEN N2=3; ELSE IF Q2='B' THEN N2=1; ELSE IF Q2='C' THEN N2=2; IF Q3='A' THEN N3=2; ELSE IF Q3='B' THEN N3=1; ELSE IF Q3='C' THEN N3=3; IF Q4='A' THEN N4=1; ELSE IF Q4='B' THEN N4=2; ELSE IF Q4='C' THEN N4=3; ...and so on...

After about 75 of these, I started to worry that there was an error in the original line of code ;), was hoping that I did not make an error in hand typing the AB&C numerical values, and I felt like this solution was way more time-intensive than it may have needed to be.

Is there some way that I could have written this such that I could have created a looping function that would translate the letters into numbers based on the table data?

I appreciate any light you can shed on this process as I'm sure this is not the last time I will need to perform this task!

Thank you,

Michelle