Loop through data set

[frupaul]

Hi Everyone,

I have the below table. My objective is to compare values Question1 and Question2 to values of T_Question1 and T_Question2 respectively. In order to do this, i intend to standardise the values of the questions. E.g Where we see [Staff_Name] or [Staff_Title] or[Company_Name] in any of the quetions prefixed with a T, i want to replace it with the value either Question1_Field_value or Question2_Field_value.

-Question1_Field is the field that is hardcoded in T_Question1 e.g Staff_Name and Staff_Title

- Question2_FIeld is the field that is hardcoded in T_Question2 e.g Company_Name

-Question1_Field_Value is the values associated with Question1_Field e.g Staff_Name = 'Abel James' for record1 etc.

TASK:

- My objective is to loop through these record since they are for the same survey and end up with one record at the end which has comparable values for the survey questions.

- To Achieve this, when SAS reads record 1, it should replace [Staff_Name] in T_Question1 with Question1_Field_Value (i.e Abel James).

- SAS should retain this for the second iteration.

- In the second iteration, SAS should [Staff_Title] in the same question with Manager (from Field: Question1_Field_Value.)

- SAS should retain this for third iteration.

- In 3rd iteration, SAS should replace [Company_Name] with Tesco (From Field: Question2_Field_Value).

- After this iteration, the output should only have 1 record with comparable values for both sets of questions.

Source code:

data Survey;
infile datalines dlm='|' missover;
length Survey_ID Question1 $70 Question2 $70 T_Question1 $70 T_Question2 $70 Question1_Field $20 Question_1_Field_Value $20 Question2_Field $20 Question2_Field_value $20;
input Survey_ID Question1 $ Question2 $ T_Question1 $ T_Question2 $ Question1_Field $ Question_1_Field_Value $ Question2_Field $ Question2_Field_value $;
cards;
1|How likely are you to recommend Abel James your Manager?|Are you satisfied with Tesco?|How likely are you to recommend [Staff_Name] your [Staff_Title]?|Are you satisfied with [Company_Name]?|Staff_Name|Abel James||
1|How likely are you to recommend Abel James your Manager?|Are you satisfied with Tesco?|How likely are you to recommend [Staff_Name] your [Staff_Title]?|Are you satisfied with [Company_Name]?|Staff_Title|Manager||
1|How likely are you to recommend Abel James your Manager?|Are you satisfied with Tesco?|How likely are you to recommend [Staff_Name] your [Staff_Title]?|Are you satisfied with [Company_Name]?|Staff_Title|Manager|Company_Name|Tesco
;

Expected output:

Any ideas on how to accomplish this are welcome:

[RW9 · Accepted solution]

I wonder why you have that dataset in the first place, doesn't seem the best modelling.  Anyways:

data Survey;
  infile datalines dlm='|' missover;
  length Survey_ID T_Question1 $70 T_Question2 $70 Question1_Field $20 Question_1_Field_Value $20 Question2_Field $20 Question2_Field_value $20;
  input Survey_ID T_Question1 $ T_Question2 $ Question1_Field $ Question_1_Field_Value $ Question2_Field $ Question2_Field_value $;
cards;
1|How likely are you to recommend [Staff_Name] your [Staff_Title]?|Are you satisfied with [Company_Name]?|Staff_Name|Abel James||
1|How likely are you to recommend [Staff_Name] your [Staff_Title]?|Are you satisfied with [Company_Name]?|Staff_Title|Manager||
1|How likely are you to recommend [Staff_Name] your [Staff_Title]?|Are you satisfied with [Company_Name]?|Staff_Title|Manager|Company_Name|Tesco
;
run;

data want;
  set survey;
  by survey_id;
  length question1 question2 $200;
  retain question1 question2;
  if first.survey_id then do;
    question1=t_question1;
    question2=t_question2;
  end;
  if question1_field="Staff_Name" then question1=tranwrd(question1,"[Staff_Name]",strip(question_1_field_value));
  if question1_field="Staff_Title" then question1=tranwrd(question1,"[Staff_Title]",strip(question_1_field_value));
  if question2_field="Company_Name" then question2=tranwrd(question2,"[Company_Name]",strip(question_2_field_value));
  if last.survey_id then output;
run;

[frupaul]

Respect mate!!