topic Re: Rearranging a Character String into specific, non-alphabetical order in SAS Programming

Rearranging a Character String into specific, non-alphabetical order

Burton_Gustice — Tue, 06 Jul 2021 20:58:05 GMT

All,

I'm currently working with some data that runs through various different codes, each of which may append a new value to some flag field. At the end of it all, we want to set a hierarchy of the flags already applied. At the moment I'm doing so by using a bunch of if find(flag, '*') gt 0 then ... statements, but I'm wondering if there's an easier way. My thought is if I can sort Flag in order of my hierarchy then I can basically just grab the left-most character and use that. I found this post to get me started on how I could sort the Flag field alphabetically, but I'm wondering if there's some way to expand that beyond just alphabetical?

Data and hierarchy logic I have

Flag	Hierarchy
QVRS	R
BEWC	C
RT	R
QTS	T

if find(flag, 'R') gt 0 then Hierarchy = 'R'; else if find(flag, 'C') gt 0 then Hierarchy = 'C'; else if find(flag, 'W') gt 0 then Hierarchy = 'W'; else if find(flag, 'T') gt 0 then Hierarchy = 'T'; else ...

Data and hierarchy logic I want

Flag	Hierarchy
RQVS	R
CEBW	C
RT	R
TQS	T

Hierarchy = substr(flag, 1, 1);

Also, in case it matters, I'm using SAS 9.4.

Re: Rearranging a Character String into specific, non-alphabetical order

Astounding — Tue, 06 Jul 2021 21:11:36 GMT

If you always want the first character of FLAG, you could use:

length hierarchy $ 1;

hierarchy = flag;

There's only room to store one character, so the first character gets stored.

Another possibility: don't bother to create HIERARCHY. Just use FLAG as is. For example:

proc freq data=have;

tables flag;

format flag $1.;

run;

Re: Rearranging a Character String into specific, non-alphabetical order

FreelanceReinh — Tue, 06 Jul 2021 21:58:32 GMT

Hello @Burton_Gustice and welcome to the SAS Support Communities!

@Burton_Gustice wrote:

I'm currently working with some data that runs through various different codes, each of which may append a new value to some flag field.

I think this is where a change would make your task easier: Better append a new observation to some dataset, i.e., work with a "long" ("vertical") data structure, with observations containing one value (see dataset HAVE below).

Then it's much easier to create any desired sort order (using PROC SQL's powerful ORDER BY clause). It's also easy to create the string of concatenated values from there, if needed (see the last DATA step below).

Example:

data have;
input id val $1.;
cards;
1 Q
1 V
1 R
1 S
2 B
2 E
2 W
2 C
3 R
3 T
4 Q
4 T
4 S
;

proc sql;
create table sorted as
select * from have
order by id, find('RTQCEBWVS',val);
quit;

data want(drop=val);
length flag $8 hierarchy $1;
do until(last.id);
  set sorted;
  by id;
  flag=cats(flag,val);
  if first.id then hierarchy=val;
end;
run;

The string in the first argument of the FIND function should reflect the hierarchy of flags (i.e., 'R' has the highest priority, etc.). It's important that all possible "values" are contained in that string. Otherwise, additional code would be needed to deal with "unexpected" values. (It's probably a good idea to add such code anyway to make the program more robust.)

Of course, you can also create the "long" dataset from your existing data if you don't want to change the process upstream.

Re: Rearranging a Character String into specific, non-alphabetical order

mkeintz — Wed, 07 Jul 2021 02:52:46 GMT

Store the ordered list of all possible codes in a retained variable _ORDERED_FLAGS. Then rebuild each flag string by looping through _ORDERED_FLAGS looking for matches in the initial, unordered, value of FLAG:

data have;
  input flag $4. ;
datalines;
QVRS
BEWC
RT
QTS
run;

data want (drop=_: i);
  set have;

  retain _ordered_flags 'RTQVSCEBW';

  _tmp_flag=flag;
  call missing(flag);

  do i=1 by 1 until(length(flag)=length(_tmp_flag));
    if findc(_tmp_flag,char(_ordered_flags,i)) then flag=cats(flag,char(_ordered_flags,i));
  end;
  length hierarchy $1;
  hierarchy=flag;
run;

Make sure all possible flag values are in the retained value for _ORDERED_FLAGS. I had to guess based on your example.

Also, this code assumes that no individual flag code appears more than once.in each FLAG string.

Also, because HIERARCHY is given a length of 1, the simple assignment

  hierarchy=flag;

will copy only the first letter.

Re: Rearranging a Character String into specific, non-alphabetical order

Burton_Gustice — Wed, 07 Jul 2021 03:34:52 GMT

I think this one makes the most sense to me and works the best/is the easiest to integrate with the way my process is currently set up. Thank you!!