BookmarkSubscribeRSS Feed
RamKumar
Fluorite | Level 6

I was wondering for Proc SQL code to generate the report with first row and last 5 rows from 20 rows.  Let me know if it is possible to achieve in single data step instead of creating multiple datasets.

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, you can, don't know why you would want to do it this way though:

proc sql;
     create table WANT as
     select     *,
                    MONOTONIC() as ID
     from       SASHELP.CARS
     where     CALCULATED ID in (1,16,17,18,19,20);
quit;

Or if you don't know how many obs:

proc sql;
  select  NOBS
  into    :NO
  from    SASHELP.VTABLE
  where   LIBNAME="SASHELP"
    and   MEMNAME="CARS";
  create table WANT as
  select  *,
          MONOTONIC() as ID
  from    SASHELP.CARS
  where   CALCULATED ID=1 or CALCULATED ID >= (&NO.-4);
quit;

BrunoMueller
SAS Super FREQ

Find below a DATA Step example.

The first steps creates data for validation, the second steps has the actual row selection using a subsetting IF

data have;
  rowId + 1;
 
set sashelp.Class;
run;

data want;
  set have nobs=_nObs;
  if _n_ = 1 or _n_ >= (_nObs - 4);
run;
venkatnaveen
Obsidian | Level 7

can u explain this step

nobs=_nObs;
  if _n_ = 1 or _n_ >= (_nObs - 4);


I am not getting?

BrunoMueller
SAS Super FREQ

During compilation of the DATA step the number of observations is read from the input data set and stored in the variable _nObs.

During execution for each iteration of the DATA step loop _n_ is increased by one.

The IF without a THEN is called a Subsetting IF.

venkatnaveen
Obsidian | Level 7

Thanks for that reply.

Now clarified.

I m  a beginner

Peter_C
Rhodochrosite | Level 12

Using features that are limited to sas datasets, here is an sql approach

%let table = sashelp.class;

Proc sql noprint ;

Select ( nobs-4) into :last_part from dictionary.tables

where libname = "%upcase(%scan(&table,1,.))"

And memname = "%upcase(%scan(&table,-1))"

;

Create table subset as

select * from &table( obs=1)

union all

select * from &table(firstobs=&last_part)

;

quit;

Limited to SAS datasets because probably only these support the firstobs=

sas_lak
Quartz | Level 8

/* Try this,  to get first record and 5th record from last */

proc sql;

create table first_1_last5_record (drop=SL_NO) as select

monotonic() as SL_NO,*  from HAVE

having monotonic()=min(monotonic(SL_NO)) or

       monotonic()=MAX(monotonic(SL_NO))-4

    ;

quit;

jakarman
Barite | Level 11

Would not advice any approach using SQL for getting first and last records when you have no ordering.
SQL is designed for getting records out of a DBMS without being aware of any physical ordering.
By that the multi-threading (parallel processing) is allowed. The use of monotonic as hidden feature is for the same reason not reliable and should be avoided.

There are more design differences with SQL and SAS-datastep to be aware off. One of them is Null value and the SAS missing. These are having a different logical background. Often being confused as equal. 

See: http://en.wikipedia.org/wiki/Null_(SQL)

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yep, that's why I couldn't understand why he would want such an ad-hoc sample as obs xyz.  Doesn't make sense in any system.  Better to use some categorization and subset that way.  Its also not necessarily reproducible if needed to be re-run or validated. 

Ksharp
Super User

You need to generate an index variable like :

data class;
 set sashelp.class;
 n+1;
run;

proc sql;
select count(*) into : n from class;
select * from class where n in (  1 %eval(&n-4) : &n  ) ;
quit;

Xia Keshan

jakarman
Barite | Level 11

Yes the obs and nobs features are very dedicated to SAS datasets. Then that is very dedicated solution not applicable with external RDBMS tables.
"How does color 9 smell."  If the question is not really a good one my preference is to ask why and go for the issue behind that. It is not always appreciated to do that. 

---->-- ja karman --<-----
Peter_C
Rhodochrosite | Level 12

Jaap

where a poster requests sql without specifying which database is the target I presume SAS.

I belive my answer is as valid as the question. (pardon my very smug answer for what looks more like a teacher's test than any operational difficulty)

regards

Peter

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 18826 views
  • 5 likes
  • 8 in conversation