DATA Step, Macro, Functions and more

data cleaning character variables with array

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 142
Accepted Solution

data cleaning character variables with array

Hi!  Need some help.  I have am doing some data cleaning on a list of CHARACTER variables (which I will later turn to numeric- but do not want to yet).

 

The expected range of raw data: missing, 0 to 10, and some random character alpha values.
 I need the expected range of cleaned numeric variable: missing, 0 to 10

 

 

I am trying to do this with an array, but not working correctly.  Basically, if the variable is between 1-10 then leave it as is.  If it is not, then set to missing.

 

data rr.recovperson2006 (keep = VHFUHFCommACFT VHFUHFCommShip VHFUHFNavACFT VHFUHFNavShip gpsACFT gpsSHIP) ;
    set rr2006labels;
        array change _character_;
        do over change;
              if (change>=0 and change<=10) then change=change;
                else change=' ';
        end;
run;

 

Help is appreciated!!

 


Accepted Solutions
Solution
‎07-27-2017 12:56 PM
Super User
Posts: 11,343

Re: data cleaning character variables with array

It helps to show some actual starting data, dummy is fine as long as it behaves like your real data, and the result for the given data. Best is to provide data step code so we can test code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Here's one approach to what I think you are doing.

data rr.recovperson2006 (keep = VHFUHFCommACFT VHFUHFCommShip VHFUHFNavACFT VHFUHFNavShip gpsACFT gpsSHIP) ; 
    set rr2006labels;
     array change _character_;
     do over change;
           if input(change,?? best.) not in (0:10) then call missing(change);
     end;
run;

You might watch out using the "Do Over" as that is no longer documented and may not be supported in future releases of SAS.

 

It also doesn't work very well if you need to reference items in two or more arrays.

View solution in original post


All Replies
Solution
‎07-27-2017 12:56 PM
Super User
Posts: 11,343

Re: data cleaning character variables with array

It helps to show some actual starting data, dummy is fine as long as it behaves like your real data, and the result for the given data. Best is to provide data step code so we can test code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Here's one approach to what I think you are doing.

data rr.recovperson2006 (keep = VHFUHFCommACFT VHFUHFCommShip VHFUHFNavACFT VHFUHFNavShip gpsACFT gpsSHIP) ; 
    set rr2006labels;
     array change _character_;
     do over change;
           if input(change,?? best.) not in (0:10) then call missing(change);
     end;
run;

You might watch out using the "Do Over" as that is no longer documented and may not be supported in future releases of SAS.

 

It also doesn't work very well if you need to reference items in two or more arrays.

Super User
Super User
Posts: 7,039

Re: data cleaning character variables with array

Doesn't sound like you are actually cleaning the data. Just converting it to numeric.

If so then what is the point?

 

data test;
  input str $32.;
  num= input(str,??32.);
  if not (num in (0:10)) then num=. ;
  if missing(num) and not missing(str) then 
    put 'NOTE: Unable to convert ' str :$quote. 
  ;
cards;
1
2
4.5
Fred
.
A
;
NOTE: Unable to convert "4.5"
NOTE: Unable to convert "Fred"
NOTE: Unable to convert "A"
NOTE: The data set WORK.TEST has 6 observations and 2 variables.

It is pretty simple to wrap that into a DO loop to process and array of variables.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 98 views
  • 0 likes
  • 3 in conversation