topic Re: Fetching previous row value to use in calculation in PROC SQL in SAS Programming

Fetching previous row value to use in calculation in PROC SQL

adisal — Thu, 15 Oct 2020 04:23:36 GMT

Hello ,

I am trying to calculate a value where I have to pick the value of A of the current rank and divide it with column B value of previous rank i.e. to calculate value of Rank 2 (Column C) = 164/13610.

The calculation has to be done in proc sql.

Rank	A	B	C
1	148	13610
2	164	14829	164/13610
3	219	19566	219/19566
4	247	24494	247/19566
5	475	26909	475/24494
6	576	31694	576/26909

Please help how to do the calculation for each rank . Thanks.

Re: Fetching previous row value to use in calculation in PROC SQL

andreas_lds — Thu, 15 Oct 2020 05:21:02 GMT

It has been explained numerous times in this week alone, that calculation requiring row-order can't be done with proc sql - at least not with easy to read/maintain code. So here is a data-step solving your problem:

data want;
  set have;
  _b = lag(b);
 
  if _n_ > 1 then do;
    c = a / _b;
  end;

  drop _b; /* EDIT */
run;

Re: Fetching previous row value to use in calculation in PROC SQL

jimbarbour — Thu, 15 Oct 2020 05:30:22 GMT

If the ranks in the first column, "Rank", are always sequential and always in order, then you could code the below. I'm joining the table to itself.

If the ranks are not always sequential, then a column would have to be added that would start with one and then increment by one until the end of the table.

Jim

DATA	Unranked_Data;
	INFILE	DATALINES	DSD	DLM=','	MISSOVER;

	INPUT	Rank	
			A	
			B
			;

DATALINES;
1,	148,	13610,	 
2,	164,	14829,	13610
3,	219,	19566,	14829
4,	247,	24494,	19566
5,	475,	26909,	24494
6,	576,	31694,	26909
;
RUN;

PROC	SQL	NOPRINT;
	CREATE	TABLE	Ranked_Data	AS
		SELECT	Rank.Rank
				,Rank.A
				,Rank.B
				,(Rank.A/Prev.B)	AS	C	FORMAT	12.6
			FROM			Unranked_Data	Rank
			LEFT	JOIN	Unranked_Data	Prev
				ON	Rank.Rank	=	Prev.Rank +  1
				;
QUIT;

Re: Fetching previous row value to use in calculation in PROC SQL

Kurt_Bremser — Thu, 15 Oct 2020 06:04:01 GMT

If this is anything other than a homework exercise to get you thinking,

DONT'T DO IT.

The tool for such actions is the data step, period.