topic substr in SAS Enterprise Guide

substr

Sreelekha — Thu, 30 Nov 2017 07:45:57 GMT

I have a requirement where the String has to be masked with the letter 'x'.

for. eg. Sreelekha should be 'xxxxxxxxx'.

I used the substr function to do this:

For a certain, column in a table where the length of the string is quite long, the string is getting replaced with just 1 X. ( x).Please refer screenshot.

Can you please help me with this issue.

Re: substr

Kurt_Bremser — Thu, 30 Nov 2017 07:48:41 GMT

Post example data (in a datastep, see https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712) and your code. Follow the advice in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce for posting code.

Re: substr

Sreelekha — Thu, 30 Nov 2017 07:55:28 GMT

Please find the code being used below.

%macro macro_name(col_name);

slen = length (&col_name);

i=0;

do until( i > slen);

substr(&col_name,i,slen) = "x";

i=i+1;

end;

%mend;

data table_name (drop=slen);

set mast_table;

%macro_name(Column1);

%macro_name(Column2);

drop i;

run;

I am facing issue with Column2.

Re: substr

Kurt_Bremser — Thu, 30 Nov 2017 08:02:53 GMT

Works for me:

%macro macro_name(col_name);
slen = length(&col_name);
i = 0;
do until (i > slen);
  substr(&col_name,i,slen) = "x";
  i = i + 1;
end;
%mend;

data test;
x1 = 'Sreelekha         ';
%macro_name(x1);
run;

proc print data=test noobs;
run;

Result:

   x1        slen     i

xxxxxxxxx      9     10

If your results differ, post your example data as requested.

Note that I used the "little running man" icon to post code, as shown in the link I gave you. Please follow advice.

The main posting window changes code layout and makes copy/pasting difficult.

Re: substr

Sreelekha — Thu, 30 Nov 2017 08:24:15 GMT

I will use that.

Apologies for missing out.

Can you please try the string " Abcd Efghijklmn , Schools and Board of Management ".

Re: substr

Kurt_Bremser — Thu, 30 Nov 2017 08:46:00 GMT

Look at the log. Your use of slen as the third parameter of the substr() function causes problems.

Since you just want to overwrite a string from start to last non-blank character, you can simplify your macro a lot:

%macro macro_name(col_name);
&col_name = repeat("x",length(&col_name));
%mend;

which also has the advantage that no additional variables are created.

PS the whole thing is now so simple that you will need a macro only when you want to reuse that code line a LOT. If you only need it for three variables, write the original code into the data step; makes for easier maintenance.

Re: substr

RW9 — Thu, 30 Nov 2017 09:16:31 GMT

You have fallen into the trap of many SAS programmers, in your immediate need to jump into macro programming, Macro is not the programming language. Use Base SAS which is the programming language and you will find your coding far simpler:

data have;
  length a $10;
  a="abc345kl"; output;
  a="tyhg222"; output;
  a="aa"; output;
run;

data want;
  set have;
  a=prxchange("s/[^A-Z ]/X/",-1,a);
run;

Re: substr

Sreelekha — Thu, 30 Nov 2017 10:18:55 GMT

Hello Kurt,

Thanks a lot 🙂 This worked.

I need it for updating 20 tables, hence would be using it as a macro.

Re: substr

Sreelekha — Thu, 30 Nov 2017 10:20:08 GMT

Thank you for the assistance.

I need to update 20 tables and hence many columns.

So. I would need a macro.

Thank you..!!

Re: substr

RW9 — Thu, 30 Nov 2017 10:34:38 GMT

Is there some pattern to the datasets/varaibles, or just all datasets in one library and all character varaibles?

data _null_;
  set sashelp.vcolumns (where=(libname="WORK"));
  by memname;
  if first.memname then call execute(cats('data work.',memname,'; set work.',memname,';'));
  if type="char" then call execute(name,'=prxchange("s/[^A-Z ]/X/",-1,',name,');');
  if last.memname then call execute(';run;');
run;

This will run through every dataset in work and convert every character variable to the X's.

Re: substr

Sreelekha — Thu, 30 Nov 2017 13:25:08 GMT

There is a where condition using which I will be filtering out the desired data. So, it is ideally 20 tables and the specified columns from each table which have to be masked with 'XX..'

Thanks for this code as well :).