by variable grouping

[SASAna]

 Hi SAs Users,

Needed help in validating the below code  ( group by claim and sum the dollar field & check for procedures by claim line level and flag them)

proc sort data = test; by CLM_ID ; run;

data test1;
set test
by clm_id;
IF sum(net_amt_pay) >= 100 THEN
DO;
IF trim(PROC_CD) in (&PROCS)
) THEN
DO; FLAG = 'A'; END;
ELSE DO; FLAG ='B'; END;
END;
ELSE FLAG ='B ';

run;

Example -  expected result of Flag would be like this - 

clm_id      net_amt_pay          proc               flag

1                     10                     1234               A

1                      50                                           B             

1                       40                                          B

Thanks,

Ana

1

[art297]

You didn't provide a have dataset, let us know how you are setting &procs, or what values it would have. Also, your code talks about a PROC_CD field, but you output contains a proc field.

That said, IF sum(net_amt_pay) >= 100 won't do what you expect. One way to get it to work the way you want is to restructure you code using a DOW loop, first to obtain the sum, and then to assign the flags.

Art, CEO, AnalystFinder.com

[Astounding]

Everything @art297 said is true.

Here's a thought for how you could proceed.

Your program doesn't work properly, not even for the four-line data set that you posted.  Come up with a program that works for that four-line data set.  Then we can talk about how you might validate it for a larger data set.

[SASAna]

I broke the code to 3 segments -
proc sql noprint;
create table test as
select
clm_id
from CLMS
group by clm_id
having sum(net_amt_pay) < 100;
quit;

proc sql noprint;
create table test1 as
select * from CLMS
where clm_id not in (select distinct clm_id from test);
quit;

data finale;
set test1;
IF (trim(PROC_CD) in (&PROCS))
THEN DO; FLAG = 'A'; END;
ELSE DO; FLAG ='B '; END;
run;

[ballardw]

Also it may help to post code in a code box to preserve formatting. Open a code box using the {i} menu icon at the top of the message box. The main message box will reformat code somewhat, usually removing blanks.

Using a consistent indenting style will make it easier to understand the code such as:

data test1;
   set test
   by clm_id;
   IF sum(net_amt_pay) >= 100 THEN   DO;
      IF trim(PROC_CD) in (&PROCS) THEN  DO; 
         FLAG = 'A'; 
      END;
      ELSE DO; 
         FLAG = 'B'; 
      END;
   END;
   ELSE FLAG = 'B ';

run;

[SASAna]

thanks for the suggestion. I will use codebox going fwd.

Thanks,
Ana