topic Re: Get the last record if the variable = a particular value in SAS Programming

Get the last record if the variable = a particular value

Q1983 — Fri, 13 Sep 2019 16:47:42 GMT

data have;

format mydate date9.;

input ln_no $ code $ mydate date9.;

datalines;

1123 C 1jun2018

1123 C 11jun2018

1123 A 15jun2018

1123 A 12jun2018

1123 R 9jun2018

1123 R 3jun2018

1123 A 3May2018

;

run;

proc sort data= have out= have2; by ln_no descending mydate;run;

I am attempting to do the following:

The dataset reads from bottom up. I want to show all instances of code = 'A' regardless.

I only want to show the first instance of a code = 'C' or code = 'R' that occurred after the 'A' code status. So in this case I need to show rows 1,2,5,6 and 7.

Rows 3,4,5 would be eliminated.

is there a way to do this within rows??? Essentially I do not want to read consecutive 'R' or 'C' rows unless an 'A' status appeared.

Re: Get the last record if the variable = a particular value

PGStats — Fri, 13 Sep 2019 17:23:12 GMT

Would it be fair to say that you want to get the last record with code R or C that occured before a code A, and that code A record?

Re: Get the last record if the variable = a particular value

Q1983 — Fri, 13 Sep 2019 17:26:43 GMT

I want all A records regardless
I want the C or R records that occurred directly after the A record. In my dataset, several R and C records show up. I want to show just the one that occurred after the A record for counting puposes. I want to count all A records and just the first C or R

Re: Get the last record if the variable = a particular value

JosvanderVelden — Fri, 13 Sep 2019 17:36:41 GMT

Something like this?

proc sort data= have out= have2; by ln_no mydate;run;

data want(keep= ln_no code mydate);
   set have2;
   retain first_C first_R;
   if first.ln_no then do;
      first_C = 0;
      first_R = 0;
      end;
   if code eq 'A' then output;
   else if code eq 'C' and first_C eq 0 then do; 
      output;
      first_C = 1;
      end;
   else if code eq 'R' and first_R eq 0 then do;
      output;
      first_R = 1;
      end;
   by ln_no mydate;
run;

Re: Get the last record if the variable = a particular value

PGStats — Fri, 13 Sep 2019 18:07:28 GMT

Is this it?

data have;
format mydate date9.;
input ln_no $ code $ mydate date9.;
datalines;
1123 C 1jun2018
1123 C 11jun2018
1123 A 15jun2018
1123 A 12jun2018
1123 R 9jun2018
1123 R 3jun2018
1123 A 3May2018
;

proc sort data= have out= have2; 
by ln_no mydate;
run;

data temp / view=temp;
set have;
grp + (code = "A");
run;

data want;
do until(last.grp);
	set temp; by grp;
	select (code);
		when ("A") output;
		when ("C", "R") do;
			if not found then output;
			found = 1;
			end;
		otherwise;
		end;
	end;
drop grp found;
run;

proc print data=want; run;

Obs. 	mydate 	ln_no 	code
1 	01JUN2018 	1123 	C
2 	15JUN2018 	1123 	A
3 	12JUN2018 	1123 	A
4 	09JUN2018 	1123 	R
5 	03MAY2018 	1123 	A