topic Re: Set flag for each record based on first obs in SAS Programming

Set flag for each record based on first obs

Krueger — Mon, 23 Dec 2019 16:48:31 GMT

Hi all, I'm pretty sure I have a simple request. If the first.ID and princpal <= 0 then set remove = 1.

What I have done successfully does this for the first ID but I need it to then apply that remove = 1 to ALL subsequent columns by that ID regardless of those row values.

Below is Have/Want and what I did (which only does the first record). Any help is appreciated.

data have;
infile datalines delimiter=",";
input ID DATE:MONYY7. PRINCIPAL;
format DATE monyy7.;
datalines;
1,SEP2019, -10
1,JUL2019, 0
1,AUG2019, 0
2,JAN2019, 0
2,FEB2019, 0
3,JAN2019, 1
3,FEB2019, 0
3,MAR2019, 0
;
run;

data want;
infile datalines delimiter=",";
input ID DATE:MONYY7. PRINCIPAL, remove;
format DATE monyy7.;
datalines;
1,SEP2019, -10,1
1,JUL2019, 0,1
1,AUG2019, 0,1
2,JAN2019, 0,1
2,FEB2019, 0,1
3,JAN2019, 1,0
3,FEB2019, 0,0
3,MAR2019, 0,0
;
run;

data want;
	set have;
	by ID;
	if first.ID and principal <=0 then remove=1;
run;

Re: Set flag for each record based on first obs

japelin — Mon, 23 Dec 2019 16:57:21 GMT

Hi, Krueger

check this code.

data want;
  set have;
  by id;
  retain remove;
  if first.id then do;
    if PRINCIPAL <= 0 then remove = 1;
    else remove=0;
  end;
run;

Re: Set flag for each record based on first obs

ballardw — Mon, 23 Dec 2019 17:01:35 GMT

Try:

data want;
   set have;
   by id;
   retain remove;
   if first.id then remove=(principal<0);
run;

RETAIN tells SAS to keep the value of the variable across the records. SAS will treat true comparisons as 1 and false as 0 so placing the () around the principal < 0 will return 1 or 0 as the value for remove.

Re: Set flag for each record based on first obs

Krueger — Mon, 23 Dec 2019 17:04:15 GMT

Appreciate the explanation behind this. Very helpful!

Re: Set flag for each record based on first obs

novinosrin — Mon, 23 Dec 2019 17:05:45 GMT

data have;
infile datalines delimiter=",";
input ID DATE:MONYY7. PRINCIPAL;
format DATE monyy7.;
datalines;
1,SEP2019, -10
1,JUL2019, 0
1,AUG2019, 0
2,JAN2019, 0
2,FEB2019, 0
3,JAN2019, 1
3,FEB2019, 0
3,MAR2019, 0
;
run;

data want;
 do until (last.id);
  set have;
  by id;
  if first.ID then remove=principal <=0;
  output;
 end;
run;