sorting the column values

cho16 — Tue, 11 May 2021 20:24:10 GMT

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

Re: sorting the column values

FreelanceReinh — Tue, 11 May 2021 21:22:45 GMT

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.

topic sorting the column values in SAS Programming

sorting the column values

Re: sorting the column values