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;
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.
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;
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.
Appreciate the explanation behind this. Very helpful!
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;
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: