Hi,
I like to have the output dataset first ordered by ascending Id then by descending var. Is it possible in hash?
Thanks - Linlin
data have;
input id var @@;
cards;
1 2 1 3 10 6 8 5 10 7 8 2
;
data _null_;
set have (obs=1);
dcl hash h( dataset: 'have', ordered: 'Ascending',multidata:'yes');
h.definekey('id','var');
h.definedata(all:'y');
h.definedone();
h.output(dataset:'want');
run;
I want the output like below:
Obs id var
1 1 3
2 1 2
3 8 5
4 8 2
5 10 7
6 10 6
It almost cann't. But there is a workaround way.
data have; input id var @@; cards; 1 2 1 3 10 6 8 5 10 7 8 2 ; run; data _null_; declare hash h(ordered: 'Ascending',multidata:'yes'); h.definekey('id','_var'); h.definedata('id','var'); h.definedone(); do until(last); set have end=last; _var=-1*var; h.add(); end; h.output(dataset:'want'); run;
Ksharp
It almost cann't. But there is a workaround way.
data have; input id var @@; cards; 1 2 1 3 10 6 8 5 10 7 8 2 ; run; data _null_; declare hash h(ordered: 'Ascending',multidata:'yes'); h.definekey('id','_var'); h.definedata('id','var'); h.definedone(); do until(last); set have end=last; _var=-1*var; h.add(); end; h.output(dataset:'want'); run;
Ksharp
Ksharp, very slick! though I am wondering if there is a way you can 'walk around' upon character variables.
Using Ksharp's approach one could convert a string to its octal representation and use this value for sorting.
The code example below uses only the first five characters for sorting (as the octal representation ends up as a 15 digit number). I believe this is most of the time sufficient.
data have;
input id var:$9. @@;
cards;
1 ab 1 xyz 10 D9a 8 test 10 Xyz 8 9This
;
run;
data _null_;
declare hash h(ordered: 'Ascending',multidata:'yes');
h.definekey('id','_var');
h.definedata('id','var');
h.definedone();
do until(last);
set have end=last;
_var=-1*input(put(subpad(var,1,5),octal.),15.);
h.add();
end;
h.output(dataset:'want');
run;
Patrick,
As always, I am amazing with your idea . But unfortunately you only process the first five character.
I have an alternative way.
Ksharp
Linlin: I had suggested using the rank function, but I'm retracting that suggestion as it would only work for one character strings. It worked for your example because the first character of the string was the only one that was needed to differentiate (correctly) order the strings.
Art,
I think Rank() can only handle one character ,not for a string, so it is not a good idea.
E.X.(wrong result)
data have; input id var:$9. @@; cards; 1 aa 1 ayz 10 Xaa 8 test 10 Xyz 8 This ; run; data _null_; declare hash h(ordered: 'Ascending',multidata:'yes'); h.definekey('id','_var'); h.definedata('id','var'); h.definedone(); do until(last); set have end=last; _var=-1*rank(var); h.add(); end; h.output(dataset:'want'); run;
Ksharp
Thank you Ksharp! You are always helpful. - Linlin
Thank you Patrick and Art! Both methods worked great! - Linlin
Art's method:
data have;
input id var:$9. @@;
cards;
1 ab 1 xyz 10 D9a 8 test 10 Xyz 8 9This
;
run;
data _null_;
declare hash h(ordered: 'Ascending',multidata:'yes');
h.definekey('id','_var');
h.definedata('id','var');
h.definedone();
do until(last);
set have end=last;
_var=-1*rank(var);
h.add();
end;
h.output(dataset:'want');
run;
proc print;run;
Here is. But I don't like it yet.
data have; input id var & $9. @@; cards; 1 a bc 1 a cb 10 D9a 8 test 10 Xyz 8 9This ; run; data _null_; if 0 then set have; declare hash h1(dataset:'have',ordered: 'Ascending'); declare hiter hi1('h1'); h1.definekey('id'); h1.definedata('id'); h1.definedone(); if 0 then set have(rename=(id=_id)); declare hash h2(dataset:'have(rename=(id=_id))',ordered:'D',multidata:'Y'); declare hiter hi2('h2'); h2.definekey('_id','var'); h2.definedata('_id','var'); h2.definedone(); declare hash h3(ordered:'A'); h3.definekey('count'); h3.definedata('id','var'); h3.definedone(); do while(hi1.next()=0); do while(hi2.next()=0); if id=_id then do;count+1;h3.add(); end; end; end; h3.output(dataset:'want'); run;
Ksharp
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: