Proc SQL > Delete from (Where) > Dataset attributes. (ATTRN)

[JoeMadden]

Hi All,

Working with a SAS dataset, I carry out a delete using "proc sql delete from where" however, this procedure doesn't appear to update the attribute (obs count) of the dataset.

Code below as an example;

/****************************************************/
/* Dummy data - 7 Obs
/****************************************************/
data work.chgdata;
  input account  12. charge;
  format account 14. charge  dollar7.;
  datalines;
345620135872 10
345620134522 7
345620123456 12
382957492811 3
345620134663 8
345620131455 6
345620104732 9
;
run;

/* NOTE: The data set WORK.CHGDATA has 7 observations and 2 variables. */

/****************************************************/
/* Capture dataset attribute (Number of Observations)
/****************************************************/
%let dsid=%sysfunc(open(work.chgdata));
%let obs=%sysfunc(attrn(&dsid,nobs));
%put %sysfunc(close(&dsid));
%put &obs;

/* SYMBOLGEN:  Macro variable OBS resolves to 7 */

Proc Sql;
    Delete from work.chgdata
    Where charge in (6,9);
Quit;

/* NOTE: 2 rows were deleted from WORK.CHGDATA. */

%let dsid=%sysfunc(open(work.chgdata));
%let obs=%sysfunc(attrn(&dsid,nobs));
%put %sysfunc(close(&dsid));
%put &obs;

/* SYMBOLGEN:  Macro variable OBS resolves to 7 */

Proc Sql;
    Select Count(*) into :trueobs
    From work.chgdata;
Quit;

%put &trueobs;

/* SYMBOLGEN: Macro variable TRUEOBS resolves to 5 */

My question is;

a) is this a known bug / fault / outcome?

b) is there anyway, using the Proc SQL to force the update of the dataset attributes so that I can use the attrn value of the dataset later on?

Thank you.

[Tom · Accepted solution]

You are looking at the wrong field. NOBS is how many observations are physically in the table. You want to look at NLOBS which is how many observations are logically in the table. The difference is NDEL, the number of deleted observations.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212040.htm

NDEL

specifies the number of observations in the data set that are marked for deletion.

NEXTGEN

specifies the next generation number to generate.

NLOBS

specifies the number of logical observations (the observations that are not marked for deletion). An active WHERE clause does not affect this number.

-1	the number of observations is not available.

NLOBSF

specifies the number of logical observations (the observations that are not marked for deletion) by forcing each observation to be read and by taking the FIRSTOBS system option, the OBS system option, and the WHERE clauses into account.

Tip:	Passing NLOBSF to ATTRN requires the engine to read every observation from the data set that matches the WHERE clause. Based on the file type and file size, reading these observations can be a time-consuming process.

NOBS

specifies the number of physical observations (including the observations that are marked for deletion). An active WHERE clause does not affect this number.

-1	the number of observations is not available.

[JoeMadden]

Thank you Tom, I'm an idiot for missing that! I read the ATTRN page (i think!) :smileycool: