topic Re: Show record as blank in last row in SAS Programming

Show record as blank in last row

Q1983 — Thu, 31 Dec 2020 23:34:19 GMT

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

Re: Show record as blank in last row

novinosrin — Thu, 31 Dec 2020 23:38:35 GMT

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;

Re: Show record as blank in last row

r_behata — Fri, 01 Jan 2021 01:02:58 GMT

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;

Re: Show record as blank in last row

mkeintz — Fri, 01 Jan 2021 01:31:06 GMT

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.

Re: Show record as blank in last row

s_lassen — Sun, 03 Jan 2021 13:40:51 GMT

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.

Re: Show record as blank in last row

Tom — Sun, 03 Jan 2021 16:20:09 GMT

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;

Re: Show record as blank in last row

mkeintz — Sun, 03 Jan 2021 16:44:45 GMT

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.