Compress Newline character for dynamic varaibles

Question

Dataset: Have
F1      F2

Student Section
Name    No

Dataset "Have". Data has new line character.

I need to compress the newline character from the data.

I want to do this dynamically as sometimes the "Have" dataset may contain new variables like F3,F4,F5 etc.,

I have written as macro to do this.. However it is not working as expected.

When i execute the below code, first time I am getting error as invalid reference newcnt. If i execute for second time in the same session, i am not getting error.

PFB my code:

%macro update_2(newcnt);

data HAVE;
    set HAVE;
    %do i= 1 %to &newcnt;
        %let colname = F&i;
        &colname=compress(&colname,,'c');
    %end;
run;

%mend update_2;


%macro update_1();

proc sql noprint;
    select count(*) into :cnt from dictionary.columns where libname="WORK" and memname="HAVE"; 
quit;

%update_2(&cnt)

%mend update_1;

Note: All the variables have name as F1,F2,F3,F4.,

Please tell me what is going wrong..

If there is any other procedures, please help me.

Answer 1

We created our own function to clean unwanted characters from strings using proc fcmp. In this case, our function cleans tab characters, line feeds, and carriage returns.

proc fcmp outlib=common.funcs.funcs; /* REPLACE TARGET DESTINATION AS NECESSARY */

  function clean(iField $) $200;    
    length cleaned $200;
    bad_char_list = byte(10) || byte(9) || byte(13);  
    cleaned = translate(iField," ",bad_char_list);
    return (cleaned );
  endsub;

run;

Create some test data with a new line character in the middle of it, then export it and view the results. You can see the string has been split across lines:

data x;
  length employer $200;
  employer = cats("blah",byte(10),"diblah"); 
run;

proc export data=x outfile="%sysfunc(pathname(work))\x.csv" dbms=csv replace;
run;

Run our newly created clean() function against the string and export it again. You can see it is now on a single line as desired:

data y;
  set x;
  employer = clean(employer);
run;

proc export data=y outfile="%sysfunc(pathname(work))\y.csv" dbms=csv replace;
run;

Now to apply this method to all character variables in our desired dataset. No need for macros, just define an array referencing all the character variables, and iterate over them applying the clean() function as we go:

data cleaned;
  set x;
  array a[*] _char_;
  do cnt=lbound(a) to hbound(a);
    a[cnt] = clean(a[cnt]);
  end;
run;

EDIT : Also note that fcmp may have some performance considerations to consider. If you are working with very large amounts of data, there may be other solutions that will perform better.

EDIT 6/15/2020 : Corrected missing length statement that could result in truncated responses.