first and last observations using proc sql

[deleted_user]

Hi,

i need first and last observations in by group using proc sql;

Ex:
I have three variables A,B,C. i need the out put first and last variables of B and group by A , B.
101 140 220
101 142 230
101 148 259
101 145 258
102 123 339
102 229 332
102 227 389


output:
101 140 220
101 148 259
102 123 339
102 229 332

Thanks & Regards
Slone

[LinusH]

Since SQL is a column based language, doing calculations according to row numbers is not SQL's cup of tea. Maybe you can do some complicated query using the unsupported monotonic function. But, this is so much easier done with data step. Do you have reason for not using the data step?

/Linus

[deleted_user]

Thank u LINUS

i have a dataset size 100GB and 400 variables. if i run proc sort it will take 4 hours. and agian i need to run data step for first.variable and last.variable. thats why i need other than this process.



Thank's & Regards
Slone

[LinusH]

Well, not so surprisingly, SQL needs also to do a sort before it does group by, so I don't think you are gaining so much, just because you don't explicitly code a sort.
If you are concerned about I/O, maybe you could try a SQL ORDER BY view, which probably result in fewer R/W operations. Be sure to use as much memory as possible to minimize swapping.

/Linus