turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- ARRAY

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-25-2013 11:40 AM

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

Accepted Solutions

Solution

07-25-2013
12:07 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

07-25-2013 12:07 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

07-25-2013 11:44 AM

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;

??

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

07-25-2013 11:57 AM

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;

Solution

07-25-2013
12:07 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

07-25-2013 12:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Vince28_Statcan

07-25-2013 01:59 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Vince28_Statcan

07-25-2013 03:32 PM

Hi **Vince**,

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

Thank you LinLin,Anca tilea

Thanks

Sam

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

07-26-2013 07:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Vince28_Statcan

07-26-2013 09:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

07-26-2013 11:13 PM

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;