data test;
infile datalines;
input Year $ Month $ Dept1N Dept2N ;
return;
datalines;
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100
;
run;
DATA WANT;
SET TEST;
IF _N_ = NOBS THEN MONTH EQ '';
RUN;
I want to say that if MONTH in last row is populated, then show it as a blank. In this case the last row showing 1NOV2014 would show a blank. I tried the NOBS function but no success
data test;
infile datalines;
input Year $ Month $ Dept1N Dept2N ;
return;
datalines;
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100
;
run;
data want;
set test nobs=nobs curobs=k;
if k=nobs and not missing(month) then call missing(month);
run;
Please use CUROBS, it's better
Of course, you can use _N_ too:-
data want;
set test nobs=nobs ;
if _n_=nobs and not missing(month) then call missing(month);
run;
data test;
infile datalines;
input Year $ Month $ Dept1N Dept2N ;
return;
datalines;
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100
;
run;
data want;
set test end=eof;
if eof and ^ missing(month) then call missing(month);
run;
The OP's statement of the problem includes (emphasis mine):
I want to say that if MONTH in last row is populated, then show it as a blank. In this case the last row showing 1NOV2014 would show a blank. I tried the NOBS function but no success
This description is a bit misleading, and leads to statements similar to the below, which are a bit redundant:
if eof and ^ missing(month) then call missing(month);
You can just as well use:
if eof then call missing(month);
I don't see any difference between "if MONTH in last row is populated, then show it as a blank" and the unconditional statement "make month in last row a blank".
The only situation in which "if eof and ^missing(month) then call missing(month)" is not redundant would be if the EOF month had initially realized a special missing value (like .A), and you want to avoid making it into a normal missing value.
No need to mess around with NOBS and _N_, just use END=
data want;
set test end=done;
if done then
call missing(month);
run;
Or if you are OK with overwriting the existing data, and you want to save some CPU and elapsed time, you can just replace the row in question:
data test;
modify test nobs=nobs point=nobs;
call missing(month);
replace;
stop;
run;
If your input data set is large, this can save some time.
Your current code is referencing a variable NOBS that is not defined. To have the data step create a variable with the number of observations you need to add the NOBS= option to the SET statement. The name of the variable to create is what goes after the NOBS=.
DATA WANT;
SET TEST nobs=NOBS ;
IF _N_ = NOBS THEN MONTH EQ '';
RUN;
I would add that, given the purpose of your request, using END= is a much better idea than using NOBS=. For instance, this would fail:
data want;
set sashelp.class (obs=10) nobs=class_size;
if _n_=class_size then put 'Last name is ' name;
run;
while this would succeed:
data want;
set sashelp.class (obs=10) end=end_of_class ;
if end_of_class then put 'Last name is ' name;
run;
And this is just the simplest counterexample. There are a lot more situations that would not be as readily apparent.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.