yabwon,
How about this one ?
ods select none; ods output sql_results=temp; proc sql number; select * from sashelp.class order by sex ; quit; ods select all; proc sql; create table want as select *, row - min(row) + 1 as want from temp group by sex order by row; quit;
You can try it with Cartesian product [EDIT: assuming that name doesn't have duplicates in groups]:
data dsn;
do ID = "C", "B", "A";
do name = "John1", "John5", "John3";
output;
end;
end;
run;
proc sql;
create table temp as
select a.ID, a.name, count(1) as Class
from dsn as a
, dsn as b
where a.ID = b.ID and a.name >= b.name
group by a.ID, a.name
order by a.ID, a.name
;
quit;
but why to use SQL in the first place?
Bart
You can't. Why?
If you want to be able to do that in SQL then you will need an implementation of SQL that supports windowing functions. PROC SQL only supports ANSI 1992 version of SQL which pre-dates the definition of SQL windowing functions.
proc sql;
create table have as
select *
from sashelp.class
order by sex;
quit;
proc sql;
create table want as
select *, monotonic() - min(monotonic()) + 1 as want
from have
group by sex;
quit;
Name | Sex | Age | Height | Weight | want |
---|---|---|---|---|---|
Judy | F | 14 | 64.3 | 90.0 | 1 |
Jane | F | 12 | 59.8 | 84.5 | 2 |
Joyce | F | 11 | 51.3 | 50.5 | 3 |
Barbara | F | 13 | 65.3 | 98.0 | 4 |
Carol | F | 14 | 62.8 | 102.5 | 5 |
Mary | F | 15 | 66.5 | 112.0 | 6 |
Louise | F | 12 | 56.3 | 77.0 | 7 |
Alice | F | 13 | 56.5 | 84.0 | 8 |
Janet | F | 15 | 62.5 | 112.5 | 9 |
Philip | M | 16 | 72.0 | 150.0 | 1 |
James | M | 12 | 57.3 | 83.0 | 2 |
Henry | M | 14 | 63.5 | 102.5 | 3 |
John | M | 12 | 59.0 | 99.5 | 4 |
William | M | 15 | 66.5 | 112.0 | 5 |
Alfred | M | 14 | 69.0 | 112.5 | 6 |
Jeffrey | M | 13 | 62.5 | 84.0 | 7 |
Thomas | M | 11 | 57.5 | 85.0 | 8 |
Ronald | M | 15 | 67.0 | 133.0 | 9 |
Robert | M | 12 | 64.8 | 128.0 | 10 |
I don't trust monotonic(), newer saw it documented 😉😉
Bart
Hi:
That's a good instinct (not to trust monotonic) -- there's a Tech Support note out there that explains monotonic might give undesirable results. If I can find it, I'll post the note here. https://support.sas.com/techsup/notes/v8/15/138.html
Cynthia
yabwon,
How about this one ?
ods select none; ods output sql_results=temp; proc sql number; select * from sashelp.class order by sex ; quit; ods select all; proc sql; create table want as select *, row - min(row) + 1 as want from temp group by sex order by row; quit;
@Ksharp owe you a beer. kudos! You are as good as Guru(king) @data_null__ in using the documentation. Thank you.
ODS to the rescue! 😀 😀
But still think, and agree with @Kurt_Bremser, that this is a job for data step.
Bart
With anything but small data, the ODS detour and SQL will most probably perform like watching the paint dry.
Within SAS, the tool for such a task is the data step, period.
I would write it a little differently:
data final;
set dsn;
by ID Name;
if first.ID
then class = 1;
else class + 1;
run;
If your intent is moving the code to a DBMS, then you should study that DBMS's documentation, or get help from a website that deals with that specific DB.
As long as you work with SAS, stay with the data step.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: