topic by variable grouping in SAS Programming

by variable grouping

SASAna — Wed, 09 Aug 2017 20:09:41 GMT

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

Re: by variable grouping

art297 — Wed, 09 Aug 2017 20:25:49 GMT

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

Re: by variable grouping

Astounding — Wed, 09 Aug 2017 21:02:50 GMT

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.

Re: by variable grouping

ballardw — Wed, 09 Aug 2017 21:20:16 GMT

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;

Re: by variable grouping

SASAna — Thu, 10 Aug 2017 17:59:29 GMT

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;

Re: by variable grouping

SASAna — Thu, 10 Aug 2017 18:00:18 GMT

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

Thanks,
Ana