change text value of NULL in all observations and columns in large data set to ""

Accepted Solution Solved
Reply
N/A
Posts: 1
Accepted Solution

change text value of NULL in all observations and columns in large data set to ""

Hello my first post.  I have a large dataset coming from a MS SQL server and it puts NULL value for all blanks both for characters and numerics.  I would like to write a script to change the NULL value of each NULL value in all observations and in all columns to either . or "" for both numeric and character.  Is there a way to do this in bulk?

There are many columns that have this NULL value added automatically from the datasource which I would have to manually convert or change.  Is there a easier way; 

Example dataset:

TimeCodeDiffSTUIDCOURSESumAxp
SUM 2006A452A452814140218A671SUM 2006A452
SPR 2006A453A454814142300A564SPR 2006A453
SUM 2011FAL 2010FAL 2010SID848148B121SUM 2011B012
WIN 2012FAL 2011WIN 2012NULLB123WIN 2012B122
FALL 2015SPR 2015NULL814196088B562FALL 2015B454
NULLNULLSPR 2009814167540NULLSPR 2009NULL
SUM 2014SUM 2014SUM 2014814192790B451SUM 2014B451
0FAL 2009NULL814171945A902NULLA902
SUM 2009SUM 2009SUM 2009814168028A901SUM 2009A901
SPR 2007NULLWIN 2007814153769A674SPR 2007NULL

I could write the code out for each column but there are 100's of columns that would need this manual change and this seems to be difficult to manage.  There must be a easier way.

Thanks

ak


Accepted Solutions
Solution
‎08-05-2015 09:38 AM
Occasional Contributor
Posts: 13

Re: change text value of NULL in all observations and columns in large data set to ""

I believe you could do something like this:

data result;

    set input;

    array cols _CHARACTER_;

    do over cols;

        if cols='NULL' then cols='';

    end;

run;

View solution in original post


All Replies
Contributor ndp
Contributor
Posts: 61

Re: change text value of NULL in all observations and columns in large data set to ""

You can use arrays:

data want;

set have;

array ch{*} _character_;

do over(ch);

          if ch="NULL" then ch="";

end;

run;


Solution
‎08-05-2015 09:38 AM
Occasional Contributor
Posts: 13

Re: change text value of NULL in all observations and columns in large data set to ""

I believe you could do something like this:

data result;

    set input;

    array cols _CHARACTER_;

    do over cols;

        if cols='NULL' then cols='';

    end;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 250 views
  • 3 likes
  • 3 in conversation