DATA Step, Macro, Functions and more

by variable grouping

Reply
Frequent Contributor
Posts: 95

by variable grouping

 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

PROC Star
Posts: 7,474

Re: by variable grouping

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

Super User
Posts: 5,511

Re: by variable grouping

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.

Frequent Contributor
Posts: 95

Re: by variable grouping

Posted in reply to Astounding
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;
Super User
Posts: 11,343

Re: by variable grouping

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;
Frequent Contributor
Posts: 95

Re: by variable grouping

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

Thanks,
Ana
Ask a Question
Discussion stats
  • 5 replies
  • 133 views
  • 0 likes
  • 4 in conversation