Creating variable to store a single value

[AJ_Brien]

Hello,

sample dataset provided below:

I'm trying to get the last observation of 'id' which is '893068', such that I can use that value later.

acc_no	id
6530792	813068
8524336	823068
60634079	853068
66065307	863068
32530792	873068
78530792	893068

I tried 2 approaches so far:

Approach 1: This is the code that I'm using:

proc sort data =abc; 
by id; 
run;

proc sql;
select id
 into :left
 from abc
 where last.id=1
;
quit;

I'm getting the following error:

ERROR: Unresolved reference to table/correlation name last.

Approach 2: Then I tried the following code:

Data _null_;
Set abc;
by id;
Retain id;
If last.id then 
left = id;
put left=;
run;

which runs perfectly, but when I try to use the value of left in another query, it gives me an error:

proc sql;
create table checks as 
select * from abc 
where id= left;
quit;

ERROR: The following columns were not found in the contributing tables: left.

The errors make sense to me but I'm not able to sort these. Would be great if there is a better way to do this.

Appreciate the guidance!

[PeterClemmensen · Accepted solution]

From your first piece of code, I assume you want to put the value in a macro variable. In that case do

data have;
input acc_no $	id $;
datalines;
6530792	813068
8524336	823068
60634079	853068
66065307	863068
32530792	873068
78530792	893068
;

data want;
   set have end=lr;
   if lr then call symputx('lastid', id);
run;

%put &lastid.;

[PaigeMiller]

---

@AJ_Brien wrote:

Approach 2: Then I tried the following code:

Data _null_;
Set abc;
by id;
Retain id;
If last.id then 
left = id;
put left=;
run;

which runs perfectly, but when I try to use the value of left in another query, it gives me an error:

proc sql;
create table checks as 
select * from abc 
where id= left;
quit;

ERROR: The following columns were not found in the contributing tables: left.

The errors make sense to me but I'm not able to sort these. Would be great if there is a better way to do this.

Appreciate the guidance!

---

You create a _null_ data set where variable left obtains a value. You can't use a _null_ data set in the next SQL, and left does not exist in ABC which what SQL is looking at. But anyway, @PeterClemmensen has a compact method that works.