ARRAY

[sam369]

Hi Experts,

I have a dataset like below

data  have;

input ord  desc $;

cards;

0  dfkasdf

1  qewrqwe

2  werewrqe

3  etritreter

4  cvczcvzcve

5  werewrqwer

6  rqwerqwerq

7  vczcvzvzcved

8  wereruipeewr

9  dhfewrwerq

10 wrwerwerq

11 ewryteiqwe

12 rewrbnmnbz

13 wecvxcvjzc

14 ewrwerqwe

15 itititoeoe

;

run;

I need to create a varaible call grp like below

select(ord);

    when(0,12,8,4)   grp=1;

      when(1,13,9,5)   grp=2;

      when(2,14,10,6)  grp=3;

      when(3,15,11,7)  grp=4;

      otherwise;

end;

i want the dataset in same soring order like

grp

1           0

            12

            8

            4

2           1

            13

            9

            5

3           2

            14

            10

            6

4           3

            15

            11

            7

any suggestion  On Array, SQL

Thanks

Sam

[AncaTilea]

Hi.

Would you like something like this?

data  have;

input ord  desc $;

cards;

0  dfkasdf

1  qewrqwe

2  werewrqe

3  etritreter

4  cvczcvzcve

5  werewrqwer

6  rqwerqwerq

7  vczcvzvzcved

8  wereruipeewr

9  dhfewrwerq

10 wrwerwerq

11 ewryteiqwe

12 rewrbnmnbz

13 wecvxcvjzc

14 ewrwerqwe

15 itititoeoe

;

run;

data want;

  set have;

  if ord in (0,12,8,4) then  grp=1;

  else if ord in (1,13,9,5) then  grp=2;

      else if ord in (2,14,10,6) then grp=3;

      else if ord in (3,15,11,7)then  grp=4;

run;

proc sort data = want;by grp;run;

data really_want;

  set want;

  by grp;

  if not first.grp then grp = .;

run;

??

[Linlin]

data  have;

input ord  desc $;

cards;

0  dfkasdf

1  qewrqwe

2  werewrqe

3  etritreter

4  cvczcvzcve

5  werewrqwer

6  rqwerqwerq

7  vczcvzvzcved

8  wereruipeewr

9  dhfewrwerq

10 wrwerwerq

11 ewryteiqwe

12 rewrbnmnbz

13 wecvxcvjzc

14 ewrwerqwe

15 itititoeoe

;

data have;

set have;

group=mod(ord,4)+1;

proc sort;

  by group descending ord ;

data temp1 temp2;

   set have;

   by group;

   if last.group then output temp1;

     else output temp2;

data want;

   set temp1 temp2;

   by group;

proc print;run;

[Vince28_Statcan · Accepted solution]

There are many different approaches. I assume that your real dataset could go far beyond 16 records where your ordering wants to have 0-1-2-3 respectively as the first record of the list but then "reverse" all other records within a list? i.e. if we were to extend the list to 20 you would have grp1 ordering as 0-16-12-8-4, grp2 ordering as 1-17-13-9-5 and so on.

Here's one approach I would use with the information you've provided.

proc sql;

select mod(ord,4)+1 as grp, ord, desc

from have

order by grp, (case

               when (ord GE 4) then 1/ord

               else 0

               end)

;

quit;

Making use of the fact that if a<b then 1/a>1/b and the case is used to ensure that all 3 values defining groups are put up top. This could be used with a different number of grouping, for so long as you change the 2 values "4" to whatever new number of groups your data has.

Vincent

[Linlin]

Inspired by Vencent, I simplified my code.

data  have;
input ord  desc $;
cards;
0  dfkasdf
1  qewrqwe
2  werewrqe
3  etritreter
4  cvczcvzcve
5  werewrqwer
6  rqwerqwerq
7  vczcvzvzcved
8  wereruipeewr
9  dhfewrwerq
10 wrwerwerq
11 ewryteiqwe
12 rewrbnmnbz
13 wecvxcvjzc
14 ewrwerqwe
15 itititoeoe
;

%let grp=4;

data have;
set have;
group=mod(ord,&grp)+1;
subgroup=ifn(ord<&grp,0,1);
proc sort data=have out=want(drop=subgroup);
  by group subgroup descending ord ;
proc print;run;

[sam369]

Hi Vince,

Perfect answer , that is what i am looking for , could you explain case section?

Thank you LinLin,Anca tilea

Thanks

Sam

[Vince28_Statcan]

Hi Sam,

Basically, the portion in paranthesis with the case/when I'm creating a new temporary variable (one that isn't kept for final dataset) and using it to order the dataset.

Since you wanted to get 0,1,2,3 to lead and then from there reverse order by group, I've used a somewhat clever transformation of your ord variable to achieve the result. Your ORD variable is 0,1,2,...,n and thus GE 0 for all points. As such, I know that for all x,y in {1,2,...,n} if x > y then 1/x < 1/y. So using this transform allowed me to order all events with ord 4 or more as with the condition when (ord GE 4). Since I know that all such division is still always GE 0, I simply set "all other cases" (so effectively all cases where ORD LT 4) to 0. Since the new created temporary variable is applied 2nd in the order by statement, SQL orders the following pairs starting with the first element of the pair

(1,0),(1, 1/5),(1, 1/9), (1, 1/13)

(2,0),(2, 1/6),(2, 1/10), (2, 1/14)

and so on.

If you are more familiar with data step procedures, here's how it would've looked like

data intermediate;

     set have;

     grp = mod(ord,4)+1;

     if (ord GE 4) then tempvar=1/ord;

     else tempvar=0;

run;

proc sort data=intermediate out=want(drop=tempvar);

     by grp tempvar;

run;

If you look at the tempvar column in the intermediate dataset, you will see what I've been trying to explain above. You can remove the (drop=tempvar) if you want to see it after the sort for learning purpose. I had gone with proc SQL because I believe it is slightly more efficient as the intermediate stuff needs not to be written on the disk. You won't see a difference if the dataset is small.

Hope this helps.

Vincent

[sam369]

Hi Vincent,

Thank you so much for this Breif explanation. Now i learned one new way to look the data & how to interpret.

I am bit familiar with SQL. I want to know x>y then i/x<1/y part....you explanation cleared my doubts....

Regards

Sam

[slchen]

Some elegant codes were offered, here is other methods with point.

data want;

      do i=0 to 3;

        do pickit=i+1,i+13,i+9,i+5;

          set have point=pickit nobs=total;

            if pickit>total then leave;

              group=i+1;

              output;

      end;

    end;

   stop;

   drop i;

   run;