Setting blank cells to 0 after merge

[brophymj]

I'm mergeing on claims information to a larger file called exposure

Data combined;

merge exposure(in=a) claims;

by key;

if a;

run;

There are about 20 numeric claims fields attaching on and some character fields. Where there are no claims info to attach on to a given row in the exposure, the claims field is blank, I want this to be 0 for numeric fields and N/A for character fields.

Is there a way to do this rather than going through every field like if field1 = . then field1 = 0....

[ballardw]

One approach is arrays. Without variable names/types I can't be too specific.

In the datastep code add something like this:

array claims <list the names of the numeric claims fields here>;

if <condition to determine no claims info> then do _I_=1 to dim(claims); claims[_I_]=0; end;

you can do similar for the character variables.

The condition you need might be something like

if sum(of claims(*))=0 then ..

[brophymj]

Here's my code but I'm not sure what to put inside <condition to determine no claims info>

I obviously know the condition - it's if the cell is . then populate it with 0.

Data test1;

set combined;

array claims<total_incurred total_no total_outstanding>;

if < .  > then do _I_ to dim(claims) ; claims[_I_] = 0; end;

run;

I'm getting errors such as

"too many array subscripts specified..."

"Syntax error expecting one or more..."

[Astounding]

brophymj,

Here's one piece of the puzzle.  There's a standard way to determine whether there is a matching claims record or not.  This should become part of your tool kit:

data combined;

  merge exposure (in=a) claims (in=b);

  by key;

  if a;

  if b=0 then do;   /* no claims record */

      ...

  end;

run;

You also may have to think a little harder about the solution.  Could there be a variable in the EXPOSURE data set that contains a blank?  Would it be appropriate to set that variable to 0 (or to "N/A") when there is no claims data?  That can happen if you are not careful with the coding.

Good luck.

[Ksharp]

proc stdize data=combined reponly missing=0;run;

[user24feb · Accepted solution]

If you simply modify after merging?

Data Combined;

  Modify Combined;

  Array N _NUMERIC_;

  Do over N;

    If Missing (N) Then N=0;

  End;

Run;

[Loko]

Hello,

Here is one solution

Prepare some data

data a;

length sexsmall $ 4;

set sashelp.class (obs=10);

sexsmall=sex;

rename age=agesmall weight=weightsmall height=heightsmall ;

drop sex;

run;

Working code

data b;

merge  sashelp.class (in=a) a (in=b);

array allnum  sexsmall-numeric-weightsmall;

array allchar  sexsmall-character-weightsmall;

by name;

if b=0 then do;

  do over allnum; allnum=0; end;

  do over allchar; allchar="N/A"; end;

end;

run;