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
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
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;
??
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;
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
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;
Hi Vince,
Perfect answer , that is what i am looking for , could you explain case section?
Thank you LinLin,Anca tilea
Thanks
Sam
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: