topic How to order multiple columns in SAS Programming

How to order multiple columns

superbug — Tue, 14 Jul 2020 20:14:10 GMT

Hello,

My data comes from proc transpose. the following data "have" is the first three columns, there are 200 columns after transpose, that is, COL1-COL200, each column is the itemid.

data have;
input COL1$ COL2$ COL3$;
cards;
p111 p777 p333
p444 p555 p666
p222 p222 p444
;
run;

I want to ascending order the itemid in each column, the "want" data is like following

data want;
input COL1$ COL2$ COL3$;
cards;
p111 p222 p333
p222 p555 p444
p444 p777 p666
;
run;

please help on the quick way of getting "want" data.

Thanks!

Re: How to order multiple columns

Reeza — Tue, 14 Jul 2020 20:17:28 GMT

It's likely easier to do this before you transpose and then have it transposed in the desired order. What does the data look like prior to the TRANSPOSE?
Or my solution would be transpose, sort, transpose back.

Re: How to order multiple columns

ballardw — Tue, 14 Jul 2020 20:44:45 GMT

You may need to provide more values as sorting of character values mixed with numeric elements may not sort in the order you want.

Consider P111 and P6 (assume these are valid values). What order would you want for these?

Re: How to order multiple columns

superbug — Tue, 14 Jul 2020 20:47:19 GMT

@Reeza

Before transpose, my data looks like the following, each row is the item id a certain candidate takes. The first column "formname" is exam form name, "name_item1" to "name_item200" are the item ids.

data have_before_trans;
input formname$ name_item1$ name_item2$ name_item3$;
cards;
AA01 p123 p321 p231
AA02 p245 p423 p543
AA03 p425 p326 p453
AA04 p235 p326 p423
;
run;

In each "formname", the order of of itemid in "name_item1" to "name_item200" are different. I want to order the itemids in "name_item1" to "name_item200" by "formname".

please suggest what's the easier way to do that.

Thanks much!

Re: How to order multiple columns

superbug — Tue, 14 Jul 2020 21:12:23 GMT

@ballardw

all of the variables are characters, so it should not have problem of "sorting of character values mixed with numeric elements' as you mentioned.

my have data is like this (there are 200 columns indicating item id, that is "name_item1" to "name_item200")

data have;
input formname$ name_item1$ name_item2$ name_item3$;
cards;
AA01 p111 p444 p222
AA02 p444 p111 p444
AA01 p222 p222 p333
AA02 p333 p333 p111
;
run;

data i want is like the following

data want;
input formname$ name_item1$ name_item2$ name_item3$;
cards;
AA01 p111 p222 p222
AA01 p222 p444 p333
AA02 p333 p111 p111
AA02 p444 p333 p444
;
run;

Please suggest. Thank you so much!

Re: How to order multiple columns

Reeza — Tue, 14 Jul 2020 22:00:45 GMT

Do a CALL SORT() on the array and then transpose.

data temp / view=temp;
set have_before_trans;
array _ni(*) name_item1-name_item3;
call sortc(of _ni(*));
run;

Then do your transpose. If you have missings in there it may not work as missings get sorted to the beginning.

Re: How to order multiple columns

ballardw — Tue, 14 Jul 2020 22:09:13 GMT

I hope your are right about the sort issue.

This works for the example provided data.

data have;
input formname$ name_item1$ name_item2$ name_item3$;
cards;
AA01 p111 p444 p222
AA02 p444 p111 p444
AA01 p222 p222 p333
AA02 p333 p333 p111
;
run;
data temp;
   set have;
   array n name_:;
   length item $ 5;
   do i= 1 to dim(n);
      item = n[i];
      output;
   end;
   keep formname item;
run;

proc sort data=temp;
   by formname item;
run;

data temp2;
   set temp;
   /* the magin number 3 is the original number of 
      name_item variables in the starting data
      replace ALL of the 3 with that number
   */
   array name_item(3) $ 4;
   retain name_: ;
   index = mod(_n_,3)+(3*(mod(_n_,3)=0)) ;
   name_item[index]=item;
   if index=3 then output;
   keep formname name_: ;
run;

Caveat: If you try to make the output data with a different number of output name_item variables the numbering of the sequences calculating the Index may become quite obnoxious and could have issues about the "odd" values. Such as creating 3 output columns with 10 values. Additional code would be needed to address the "last" record to create the output record with only the first name_item set.

Re: How to order multiple columns

superbug — Wed, 15 Jul 2020 02:08:13 GMT

@ballardw

The code worked for the example data. Thanks much!

After applying the code in my real data, I realized "name_item" need to be ordered by blocks, that is,

block1: name_item1-name_item50

block2: name_item51-name_item100

block3: name_item101-name_item150

block4: name_item151-name_item200

Could you please help modify your "data temp2" step?

Re: How to order multiple columns

superbug — Wed, 15 Jul 2020 03:03:47 GMT

@ballardw

My real data has 200 items, and "divided" into 4 blocks as following

block 1: name_item1-name_item50

block 2: name_item51-name_item100

block 3: name_item101-name_item150

block 4: name_item151-name_item200

When administer the test, the order of item name (name_item:) is different within each block for different candidates taking the same form_name. My purpose is after reordering item name, for candidates taking the same form_name, the order of item name are the same for all 200 items.

In my real data, I added candidate id as in the following code

data temp;
   set have;
   array n name_:;
   length item $ 10;
   do i= 1 to dim(n);
      item = n[i];
      output;
   end;
   keep formname id item;
run;

proc sort data=temp;
   by formname id item;
run;

data temp2;
   set temp;
/* the magin number 3 is the original number of 
      name_item variables in the starting data
      replace ALL of the 3 with that number
   */
   array name_item(200) $ 10;
   retain name_: ;
   index = mod(_n_,200)+(200*(mod(_n_,200)=0)) ;
   name_item[index]=item;
   if index=200 then output;
   keep form_name id name_: ;
run;

In "temp2" data I got, item order in the first block, that is, "name_item1" to "name_item50" are the same for all test taker and across all form_name, which is desired. But the item order of the rest three blocks are not quite what I wanted, in other words, item order in the 2nd block ( "name_item51" to "name_item100") are the same within each block but different across form_name. Same results to the 3rd and 4th blocks.

Could you please modify your code to help get the desired results I wanted.

Thank you!!

Re: How to order multiple columns

s_lassen — Wed, 15 Jul 2020 14:32:14 GMT

Here is code which gives you the WANT data from the HAVE as you have shown, using a "hash of hashes" (the HoH object):

data have;
input formname$ name_item1$ name_item2$ name_item3$;
cards;
AA01 p111 p444 p222
AA02 p444 p111 p444
AA01 p222 p222 p333
AA02 p333 p333 p111
;
run;

proc sort data=have;
  by formname;
run;


data want;
  set have;
  by formname;
  array cols(*) name_item:;
  
  retain index 0;
  if _N_=1 then do;
    declare hash h;
    declare hiter hit;
    
    declare hash HoH(ordered: 'Y');
    HoH.definekey('index');
    HoH.definedata('h','hit');
    HoH.definedone();
    
    do index=1 to dim(cols);
      h=_new_ hash(ordered:'Y', multidata:'Y');
      h.definekey(vname(cols(index)));
      h.definedata(vname(cols(index)));
      h.definedone();
      hit=_new_ hiter('h');
      HoH.add();
      end;
    end;
    do index=1 to dim(cols);
      HoH.find();
      h.add();
      end;
    done=0;
    if last.formname then do until(0);
      do index=1 to dim(cols);
        HoH.find();
        done=hit.next();
        if done then
          h.clear();
        end;
      if done then leave;
      output;
      end;
  drop done index;
run;

But I do not understand what you are writing about blocks of 50 columns - could you show some sample data, or explain more specifically?

Re: How to order multiple columns

ballardw — Wed, 15 Jul 2020 14:38:29 GMT

@superbug wrote:

@ballardw

The code worked for the example data. Thanks much!

After applying the code in my real data, I realized "name_item" need to be ordered by blocks, that is,

block1: name_item1-name_item50

block2: name_item51-name_item100

block3: name_item101-name_item150

block4: name_item151-name_item200

Could you please help modify your "data temp2" step?

Provide actual example data. You have now changed the input data structure at least twice.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: How to order multiple columns

superbug — Wed, 15 Jul 2020 19:28:30 GMT

@ballardw @Reeza @s_lassen

Thanks for your help and suggestions!

I figured out some other ways to get what I wanted.

Thanks to all of your experts! I am learning how to make myself clear when I ask help in this forum.