topic first and last observations using proc sql in SAS Procedures

first and last observations using proc sql

deleted_user — Wed, 20 May 2009 06:54:19 GMT

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

Re: first and last observations using proc sql

LinusH — Wed, 20 May 2009 08:50:29 GMT

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

Re: first and last observations using proc sql

deleted_user — Wed, 20 May 2009 09:48:19 GMT

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

Re: first and last observations using proc sql

LinusH — Wed, 20 May 2009 09:57:39 GMT

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