I have had to look up hundreds (if not thousands) of free-text answers on google, making notes in Excel along the way and inserting SAS-code around the answers as a last step.

The output looks like this:

This output contains an unnecessary number of blank spaces, which seems to confuse SAS's search to the point where the observations can't be properly located.

It works if I manually erase superflous spaces, but that will probably take hours. Is there an automated fix for this, either in SAS or in excel?

I tried using the STRIP-function, to no avail:

else if R_res_ort_txt=strip("   arild   ") and R_kom_lan=strip("    skåne   ") then R_kommun=strip("    Höganäs " );

CodePudding user response：

If you want to generate a string like:

 if R_res_ort_txt="arild" and R_kom_lan="skåne" then R_kommun="Höganäs";

from three variables, let's call them A B C, then just use code like:

 string=catx(' ','if R_res_ort_txt=',quote(trim(A))
                ,'and R_kom_lan=',quote(trim(B))
                ,'then R_kommun=',quote(trim(C)),';') ;

Or if you are just writing that string to a file just use this PUT statement syntax.

 put 'if R_res_ort_txt=' A :$quote. 'and R_kom_lan=' B :$quote.
     'then R_kommun=' C :$quote. ';' ;

CodePudding user response：

A saner solution would be to continue using the free-text answers as data and perform your matching criteria for transformations with a left join.

proc import out=answers datafile='my-free-text-answers.xlsx';

data have;
  attrib R_res_ort_txt R_kom_lan length=$100;
  input R_res_ort_txt ...;
  datalines4; 
  ... whatever all those transforms will be performed on...
;;;;

proc sql;
  create table want as
  select 
    have.* ,
    answers.R_kommun_answer as R_kommun
  from 
    have
  left join 
    answers
  on 
    have.R_res_ort_txt = answers.res_ort_answer
  & have.R_kom_lan     = abswers.kom_lan_answer
  ;