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.
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.
specifies the next generation number to generate.
specifies the number of logical observations (the observations that are not marked for deletion). An active WHERE clause does not affect this number.
-1 |
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.
specifies the number of physical observations (including the observations that are marked for deletion). An active WHERE clause does not affect this number.
-1 |
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.
specifies the next generation number to generate.
specifies the number of logical observations (the observations that are not marked for deletion). An active WHERE clause does not affect this number.
-1 |
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.
specifies the number of physical observations (including the observations that are marked for deletion). An active WHERE clause does not affect this number.
-1 |
Thank you Tom, I'm an idiot for missing that! I read the ATTRN page (i think!) :smileycool:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.