Flagging records based on a criteria

[devsas]

Good Afternoon everyone. Thanks in advance.  So, I have a dataset with many variables, including one for DND(do not display) flag. This dataset is created after running a macro which was not originally created by me. I have to modify this macro to include another rule so that when that happens, DND is 'Y' rather than current 'N'. The rule is that when for a certain product type, there is a provider and a specific treatment category and entity type, and the same provider within the same product type has the same treatment category but a different entity type, a specific entity type should be preferred and the other record should have DND as 'Y'. The following data should clear this up. For simplicity, I have only included variables which are of interest, actual dataset has 15-16 other variables too.

data have;

input product $ provider_no $ TC $ ENTITY_TYPE $ DND $;

DATALINES;

 MAB1 ABC001 MRI TYPE=1 N

 MAB1 ABC001 MRI TYPE =2 N

MAB1  BCD002 FLU TYPE=1 N

MAB1  BCD002 TEST TYPE=2 N

;

RUN;



DATA WANT;

input product $ provider_no $ TC $ ENTITY_TYPE $ DND $;

DATALINES;

 MAB1 ABC001 MRI TYPE=1 N

 MAB1 ABC001 MRI TYPE =2 Y

MAB1  BCD002 FLU TYPE=1 N

MAB1  BCD002 TEST TYPE=2 N

;

RUN;

Thanks so much.

[Astounding]

How do you determine which entity type is "preferred"?  The lowest value for TYPE= ?  Is it just the first one that comes along in the data, or is there some other rule?

[devsas]

Thanks. For this particular situation, whenever there is both type=1 and type =2, we should prefer type =1.

[devsas]

Actually DND needs to be changed to 'Y' only for those cases where for the same product type, provider and tc, there are both type 1 and type 2 entity types and type 2 should be changed to Y. Rest everything else remains the same.

[ScottBass]

Get rid of the extraneous space in TYPE =2.

Get your code to run correctly in SAS, then paste here, or else paste your posted code back into SAS to make sure it runs correctly.

Does this give you what you want?

data have;
input product $ provider_no $ TC $ ENTITY_TYPE $ DND $;
datalines;
MAB1 ABC001 MRI TYPE=1 N
MAB1 ABC001 MRI TYPE=2 N
MAB1  BCD002 FLU TYPE=1 N
MAB1  BCD002 TEST TYPE=2 N
;
run;

data want;
	set have;
	by product provider_no tc entity_type notsorted;  * remove notsorted if it does not apply ;
	first=first.tc;		* for debugging ;
	if first.tc then ;  * do nothing ;
	else dnd='Y';
run;

[devsas]

Thanks for the reply. Oh sorry about the mistake in my code, generally I test it before pasting here, but something slipped away this time.

I cannot test the code at this point, but not sure if this will work in case entity type has different value than either of the ones i mentioned. For instance, if two records have same product type, provider and tc code but entity values are type=1 and type =3, I dont want the DND to change in that case. In other words, only when there are two records for same, product, provider and TC code and entity types are type =1 and type =2, i want to display record with type=2 as Y for DND. Rest for everything else, DND remains the same as original.

Also, I hope the code wont change if there are additional variables, such as counts, avg_cost etc?

[ScottBass]

Sorry, in the future please state your requirements clearly and in their entirety. 

Either run with what I provided and code your own solution, or perhaps someone else will spoon feed you a solution.

Unsubscribing...

[devsas]

The following code gets it halfway- meaning it does change to Y for those cases where there are both type=1 and type=2, but it also changes DND to Y when there is only type=2 only and I dont want it to change.

 data want2;
   set have;
      by BL_BCBS_PROD_CD bl_pln_prvdr_num tc_code tc_desc;  
 if LAST.tc_code and entity_type = 'TYPE=2' then dont_disp_ind='Y' ; 
    else dont_disp_ind=dont_disp_ind;
 run;

I tried another code and though I dont any issue with it, it still doesnt solve the problem. Infact it doesnt do even what above code did. Not sure what im missing here.

data want4;
	set have;
	by BL_BCBS_PROD_CD bl_pln_prvdr_num tc_code tc_desc;  
	IF FIRST.TC_CODE THEN N=0;N= N+1;
	IF N=2 AND ENTITY_TYPE = 'TYPE=2' then dont_disp_ind='Y';
	else dont_disp_ind=dont_disp_ind;
    run;

[devsas · Accepted solution]

Just in case anyone is interested- i found the solution and it tests fine.

data want5;
   set have;
      by BL_BCBS_PROD_CD bl_pln_prvdr_num tc_code tc_desc;  
 
    if first.tc_code and last.tc_code then dont_disp_ind=dont_disp_ind;
	else if LAST.tc_code and entity_type = 'TYPE=2' then dont_disp_ind='Y' ; 
 run;