CODEWITHSUNDEEP
sasproc-sql
newRuser
newRuser

Reputation: 95

Select nth dataset from library using sas proc sql

I have a library holding monthly data and I would like to select last three months data. For the most recent data, i used the below code. The desired result is to create &data, &data1, &data2, for three months data.

PROC SQL;
SELECT MEMNAME INTO: DATA FROM DICTIONARY.TABLES
WHERE UPCASE(MEMNAME) = 'LIBNAME'
AND UPCASE(MEMNAME) LIKE 'DATA20%'
AND LENGTH(MEMNAME)=10 AND NOBS > 1
ORDER BY MEMNAME DESCENDING;
QUIT;

I tried adding where monotonic() = 2 before order by statement but it gave me the 2nd data by ascending instead. How can I extract it in a descending way.

Upvotes: 0

Views: 382

Answers (2)

Richard
Richard

Reputation: 27508

You can use the Proc SQL option OUTOBS= to restrict output to the first N rows of a result set.

Example:

PROC SQL;
reset noprint outobs=3;

SELECT MEMNAME INTO: DATA separated by ' '
FROM DICTIONARY.TABLES
WHERE LIBNAME = 'SASHELP'
AND MEMNAME LIKE 'C%'
AND NOBS > 1
ORDER BY MEMNAME DESCENDING
;
QUIT;

%put &=DATA;

Log

DATA=CP951 COUNTSERIES COMET

Upvotes: 0

PeterClemmensen
PeterClemmensen

Reputation: 4937

See if you can use this as a template

data data202001; set sashelp.class; run;
data data202002; set sashelp.class; run;
data data202003; set sashelp.class; run;
data data202004; set sashelp.class; run;
data data202005; set sashelp.class; run;
data data202006; set sashelp.class; run;
data data202007; set sashelp.class; run;
data data202008; set sashelp.class; run;

proc sql outobs=3 noprint;
   select memname into :data1 -
   from dictionary.tables
   where libname = "WORK" 
      and upcase(memname) like "DATA%"
      and nobs > 1
   order by memname desc;
quit;

%put &data1. &data2. &data3.;

Prints:

DATA1=DATA202008 DATA2=DATA202007 DATA3=DATA202006

Upvotes: 1

Related Questions