sorting the column values

[cho16]

Hi

I need help on the below thing.

There are 2 columns provider and COD. The COD will have different values EPA,EPB,HPL.

If the provider has the blank COD values it should load the record as both EPA and HPL.

If the provider has EPA and there are other values it should display it only for EPA.

If the provider has HPL and there are other values it should display it only for HPL.

If the provider has both HPL and EPA , it should display both..

Provider	COD
001
001
002
002	EPA
002	EPB
003	HPL
003	EPB
003	EPC

004           EPA

004           HPL

Output

Provider	COD
001	HPL
001	EPA
002	EPA
003	HPL

004            EPA

004             HPL

Thanks in advance

[FreelanceReinh · Accepted solution]

Hi @cho16,

The basic idea is: For each provider (BY group) collect the information about COD values of interest in flag variables or in a temporary array. This can be done in a (so called) DOW loop. Then write the appropriate observations to the output dataset according to your rules.

Example:

data have;
input Provider $ COD $;
cards;
001 .
001 .
002 .
002 EPA
002 EPB
003 HPL
003 EPB
003 EPC
004 EPA
004 HPL
;

data want(drop=_:);
do until(last.provider);
  set have;
  by provider;
  if cod='EPA' then _epa=1;
  else if cod='HPL' then _hpl=1;
  else if ~cmiss(cod) then _oth=1;
end;
_mis=~n(of _:);
if _epa | _mis then do;
  cod='EPA'; output;
end;  
if _hpl | _mis then do;
  cod='HPL'; output;
end;  
run;

You may need to adapt the second DATA step if the sort order within a BY group is important (cf. provider 001 vs. provider 004) or if duplicates or cases like _oth=1, _epa=_hpl=. (which are not present in your sample data) are to be handled differently.