topic extract N obs in proc sql in SAS Programming

extract N obs in proc sql

BrahmanandaRao — Tue, 02 Apr 2024 11:35:30 GMT

data test1;
input id state $15. ;
datalines;
111 Texas
222 Florida
333 Kansas
444 Lowa
555 Louisiana
;
run;

data test2;
input id state $15. ;
datalines;
666 Hawaii
777 Kentucky
888 Maryland
999 Michigan
110 Missouri
;
run;




data test3;
input id state $15. ;
datalines;
112  Montana
113  Neveda
114  New Jersey
115  New York
116  Virgina
;
run;


proc sql ;
    select * from test1 (obs=1) 
    union
    select * from  test3 (obs=1)
    union
    select * from test2 (obs=1);
quit;

How to extract first record from each table using proc sql i tried above code

monotonic function is undocumented so in this case how to achive with proc sql for N records pull from tables

Re: extract N obs in proc sql

Patrick — Tue, 02 Apr 2024 11:51:12 GMT

I would add the ALL keyword to your union but else your SQL looks o.k. to me as long as the source tables are in SAS and not a database.

proc sql ;
    select * from test1 (obs=1) 
    union corr all
    select * from  test3 (obs=1)
    union corr all
    select * from test2 (obs=1);
quit;

Re: extract N obs in proc sql

LinusH — Tue, 02 Apr 2024 13:06:21 GMT

What is your exact requirement?
Since you are mentioning monotonic, do wish to store the original row number?
If you're not comfortable using montonic, switch to the data step instead (_n_).
And N, does it impliy you wish to use a parameter for the no of observations to pull from each source?

Re: extract N obs in proc sql

ballardw — Tue, 02 Apr 2024 18:58:17 GMT

I think you need to provide an example of what you expect for output given a value of N other than 1.

I have no clue what you are expecting as a result.