There are about 100o records with below data structure
ID Stat value
11 n 20
11 mean 12
11 median 12.5
12 n 0
12 mean 0
12 median 0
if the stat n =0 mean and median should be set to blanks as below:
ID Stat value
11 n 20
11 mean 12
11 median 12.5
12 n 0
12 mean
12 median
Perhaps:
data want; set have; by id; retain nflag; if first.id then nflag=0; if stat='n' and value=0 then nflag=1; if nflag and stat in ('mean','median') then call missing (value); drop nflag; end;
Assumes the data is sorted by ID and that the N statistic always comes before the mean and median.
Perhaps:
data want; set have; by id; retain nflag; if first.id then nflag=0; if stat='n' and value=0 then nflag=1; if nflag and stat in ('mean','median') then call missing (value); drop nflag; end;
Assumes the data is sorted by ID and that the N statistic always comes before the mean and median.
It can done only if VALUE is a character variable. Is it? If not, it can be set a a missing value, not a blank. But you can set the system option missing="" and then see numeric missing values as a blank through whatever UI you're using. The code below should work regardless of the data type of VALUE.
First, if stat="n" always on the first record in each ID group, then:
data have ;
input ID Stat $ value ;
cards ;
11 n 20
11 mean 12
11 median 12.5
12 n 0
12 mean 0
12 median 0
;
run ;
option missing = "" ;
data want (drop = _:) ;
do until (last.id) ;
set have ;
by id ;
if first.id and put (cats (value), $1.) = "0" then _setnull = 1 ;
if _setnull and stat in ("mean", "median") then call missing (value) ;
output ;
end ;
run ;
If stat="n" (and also "mean" or "median") can be anywhere within an ID group, then, more generally:
data want (drop = _:) ;
do _n_ = 1 by 1 until (last.id) ;
set have ;
by id ;
if stat = "n" and put (cats (value), $1.) = "0" then _setnull = 1 ;
end ;
do _n_ = 1 to _n_ ;
set have ;
if _setnull and stat in ("mean", "median") then call missing (value) ;
output ;
end ;
run ;
Either way, CATS and CALL MISSING are used to make the code independent of the data type of VALUE.
Kind regards
Paul D.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.