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....
If you simply modify after merging?
Data Combined;
Modify Combined;
Array N _NUMERIC_;
Do over N;
If Missing (N) Then N=0;
End;
Run;
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 ..
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..."
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.
proc stdize data=combined reponly missing=0;run;
If you simply modify after merging?
Data Combined;
Modify Combined;
Array N _NUMERIC_;
Do over N;
If Missing (N) Then N=0;
End;
Run;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.