BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
frupaul
Quartz | Level 8

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.

Screen Shot 2018-07-26 at 12.16.36.png

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:

Screen Shot 2018-07-26 at 12.26.41.png

 

Any ideas on how to accomplish this are welcome:

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

Respect mate!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 271 views
  • 0 likes
  • 2 in conversation