topic Re: Data cleansing in SAS dataset in SAS Programming

Data cleansing in SAS dataset

Babloo — Tue, 06 Jun 2023 17:57:22 GMT

Any help with the generic program to remove any special characters (leading/trailing spaces, CR, LF) other than alphabets, underscores and numbers in each observations for all the variables?

I know we can accomplish this with COMPRESS function but I'm not certain how to make it work for all the variables in the dataset. I want to add this step in the macro program which already in place. Input dataset is dynamic and hence the variables. E.g. Dataset A can have ID and EMPLOYEE variables and Dataset B can have ID and AGE variables. Any dataset can be input for my generic program where I want to remove the special characters.

I don't want to create any new variables as part of this step.

Re: Data cleansing in SAS dataset

PaigeMiller — Tue, 06 Jun 2023 17:41:54 GMT

data want;
    set have;
    array var _character_;
    do i=1 to dim(var);
        var(i)=compress(var(i),.........);
    end;
run;

Re: Data cleansing in SAS dataset

Babloo — Tue, 06 Jun 2023 17:58:35 GMT

@PaigeMiller I don't want to create any new variables as part of this step. How to rename the variable 'i' to actual variable name?

Re: Data cleansing in SAS dataset

PaigeMiller — Tue, 06 Jun 2023 18:00:40 GMT

Please try the code and see if there are new variables created. The only new variable created is I, which you can get rid of.

Re: Data cleansing in SAS dataset

Babloo — Tue, 06 Jun 2023 18:07:46 GMT

@PaigeMiller Already tried the below program and it's not working as excepted. After removing the special characters we have to rename it to the actual variable name. If there are two variables in the dataset then I have to do the same in those two variables.

data have;
input id $;
datalines;
 124 5 
6 7 
$1k09~  
     
;
run;

data want;
    set have;
    array var _character_;
    do i=1 to dim(var);
        var(i)=compress(var(i),'090A0C0DA0'x);
    end;
run;

Excepted results:

1245
67

Re: Data cleansing in SAS dataset

PaigeMiller — Tue, 06 Jun 2023 18:51:23 GMT

Please look at data set named HAVE. The data set is not being created properly and so of course after you use COMPRESS on it, you don't get the proper results.

I add that this simple activity of LOOKING AT your own data ought to be the first step in debugging, you don't need people here in the SAS Communities to tell you that your data set is not being created properly.

Re: Data cleansing in SAS dataset

Kurt_Bremser — Tue, 06 Jun 2023 19:40:55 GMT

You don't "rename" the helper variable i. Just get rid of it with a DROP statement or DROP= dataset option.

Re: Data cleansing in SAS dataset

Babloo — Wed, 07 Jun 2023 12:15:32 GMT

@Kurt_Bremser @PaigeMiller Can I ignore this NOTE in the log?

NOTE: The array var has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as
array references and not function references.

60   data  want;
61       set want;
62       array var _character_;
63       do i=1 to dim(var);
64           var(i)=compress(var(i),'090A0C0DA0'x);
NOTE: The array var has the same name as a SAS-supplied or user-defined function.  Parentheses following this name are treated as 
      array references and not function references.
65       end;
66       drop i;
67   run;

How to tweak the COMPRESS function for NOT to remove the spaces within the string? E.g. 'Hello World' should be same as 'Hello World' and NOT 'HelloWorld'

Re: Data cleansing in SAS dataset

PaigeMiller — Wed, 07 Jun 2023 12:30:08 GMT

My mistake. To remove that NOTE from the log, I should not have told you to use an array named VAR. If you use an array named VARNAME (for example) then the NOTE does not appear in the log.

data want;
    set have;
    array varnames _character_;
    do i=1 to dim(varnames);
        varnames(i)=compress(varnames(i),'090A0C0DA0'x);
    end;
run;

About how to handle the case of "Hello World": if you want to remove leading and trailing blanks, but not remove blanks in the middle of the string, you can use the TRIM function and the LEFT function.

data want;
    set have;
    array varnames _character_;
    do i=1 to dim(varnames);
        varnames(i)=trim(left(compress(varnames(i),'090A0C0DA0'x)));
    end;
run;

Re: Data cleansing in SAS dataset

Kurt_Bremser — Wed, 07 Jun 2023 12:51:54 GMT

You should also make it a habit to not use round brackets for array references. It is common practice to use curly quotes for this.

Re: Data cleansing in SAS dataset

Babloo — Wed, 07 Jun 2023 14:22:58 GMT

@PaigeMiller I tried your code with trim and left function but it's not producing the desired results.

data have;
input id $;
datalines;
1234 
 234
123 456
!123
;
run;

data want;
    set have;
    array varnames _character_;
    do i=1 to dim(varnames);
        varnames(i)=trim(left(compress(varnames(i),'090A0C0DA0'x)));
    end;
drop i;
run;

Excepted Results:

With your code, I could see only '123' in the third observation instead of '123 456'

Re: Data cleansing in SAS dataset

PaigeMiller — Wed, 07 Jun 2023 14:25:40 GMT

Repeating my earlier statements:

Please look at data set named HAVE. The data set is not being created properly and so of course after you use COMPRESS on it, you don't get the proper results.

I add that this simple activity of LOOKING AT your own data ought to be the first step in debugging, you don't need people here in the SAS Communities to tell you that your data set is not being created properly.

Honestly, @Babloo you need to check to see if you have the right data every time you are not getting the right answer. Don't make us check for you.

Re: Data cleansing in SAS dataset

Babloo — Wed, 07 Jun 2023 14:34:58 GMT

@PaigeMiller Sample data which I shown in data step is correct and align with the real life data. Hence I looking for guidance and clarification.

Compress function

Babloo — Wed, 07 Jun 2023 17:28:38 GMT

I want to remove CR, LF and leading and trailing spaces in the string. With this code, I could see only '123' in the third observation instead of '123 456'.

I don't want to remove the spaces between the words or string.

data have;
input id $;
datalines;
1234 
 234
123 456
123
;
run;

data want;
    set have;
    array varnames _character_;
    do i=1 to dim(varnames);
        varnames(i)=trim(left(compress(varnames(i),'090A0C0DA0'x)));
    end;
drop i;
run;

Excepted result:

Also I'd like to know how to achieve this using PRXCHANGE or any other similar function.

Re: Compress function

PaigeMiller — Wed, 07 Jun 2023 17:32:17 GMT

Please make sure your data set HAVE is constructed properly and has the right data. As of now, you haven't checked to see if it is correct.

Re: Data cleansing in SAS dataset

Kurt_Bremser — Wed, 07 Jun 2023 18:24:12 GMT

@Babloo wrote:

@PaigeMiller Sample data which I shown in data step is correct and align with the real life data. Hence I looking for guidance and clarification.

A clear and resounding NO. The way you read the DATALINES, only "123" will be read into the variable, as the blank acts as a delimiter.

You need to fix that first, then you will see that @PaigeMiller 's code works.

Re: Compress function

Kurt_Bremser — Wed, 07 Jun 2023 18:27:11 GMT

I moved this back in here, as the issue is still the same. Please keep the discussion in one thread.