topic Re: Setting blank cells to 0 after merge in SAS Procedures

Setting blank cells to 0 after merge

brophymj — Wed, 20 Aug 2014 14:31:36 GMT

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....

Re: Setting blank cells to 0 after merge

ballardw — Wed, 20 Aug 2014 15:08:04 GMT

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 ..

Re: Setting blank cells to 0 after merge

Ksharp — Wed, 20 Aug 2014 15:13:54 GMT

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

Re: Setting blank cells to 0 after merge

brophymj — Wed, 20 Aug 2014 15:31:15 GMT

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..."

Re: Setting blank cells to 0 after merge

Astounding — Wed, 20 Aug 2014 15:43:05 GMT

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.

Re: Setting blank cells to 0 after merge

user24feb — Wed, 20 Aug 2014 17:18:38 GMT

If you simply modify after merging?

Data Combined;

Modify Combined;

Array N _NUMERIC_;

Do over N;

If Missing (N) Then N=0;

End;

Run;

Re: Setting blank cells to 0 after merge

Loko — Wed, 20 Aug 2014 20:12:56 GMT

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;