topic Re: Creating a new column in New SAS User

Creating a new column

Nini1 — Wed, 23 Sep 2020 14:43:49 GMT

Hi everyone,

I am a new SAS user. I want a data with 2 columns 'ID' and 'amount' and want to create a new column 'amount1' that is the last observation's value in each line.

could you please tell me how can I do it?

Thank you in advance

ID	amount	amount1
12	2000	4000
167	2500	4000
256	1300	4000
3445	4000	4000

Re: Creating a new column

novinosrin — Wed, 23 Sep 2020 14:59:23 GMT



data have;
input ID	amount;*	amount1;
cards;
12	2000	4000
167	2500	4000
256	1300	4000
3445	4000	4000
;

data want;
 set have;
 if _n_=1 then  set have(keep=amount rename=(amount=amount1)) point=nobs nobs=nobs;
run;

Re: Creating a new column

CurtisMackWSIPP — Wed, 23 Sep 2020 14:57:48 GMT

I'm not sure I understood your question, but if I did, there are two approaches.

The lag function is designed to give you the value of a variable the previous time it was called. If you call it every loop, it will give you the value from the previous row. It is easy, but can get tricky with more complicated code.

Using a retain statement is a little more work, but is much more flexible and not a tricky.

data have;
  infile datalines dsd;
  input id amount;
datalines;
12,2000
167,2500
256,1300
3445,4000
;
run;

data want_lag;
  set have;
  amount1 = lag(amount);
run;

data want_retain;
  set have;
  retain amount1 .;
  output;
  amount1 = amount;
run;

Re: Creating a new column

jimbarbour — Wed, 23 Sep 2020 15:25:55 GMT

Hello @Nini1 and welcome. 🙂

If I'm understanding you correctly, you could do the following:

DATA	Have;
	INFILE	DATALINES4
		FIRSTOBS	=	2
		DLM			=	'09'X
		DSD;
	INPUT	ID		$
			Amount	:	8.
			;
DATALINES4;
ID	amount	
12	2000	
167	2500	
256	1300	
3445	4000
;;;;
RUN;

DATA	Want;
	SET	Have	NOBS=Last_Observation;

	IF	_N_			=	1	THEN
		SET	Have	(RENAME=(Amount=Amount1))
			POINT	=	Last_Observation
			;
RUN;

Which would give you the following results:

I'm using a couple of "tricks" here:

1. _N_ is an automatic variable provided by SAS. If _N_ = 1, then it's the first record in the SET, and _N_ = 1 ensures that the code controlled by the IF statement executes once and only once.

2. I have not one but two SET statements. The first SET statement drives the process and provides the current record, one record at a time to the rest of the program. On the second SET statement, I'm using a RENAME parameter so that the value retrieved in Amount doesn't overlay the Amount from the current record. I change the name from the second SET statement from Amount to Amount1.

3. In the first SET statement, I have a NOBS parameter set to a variable, Last_Observation. Each SAS dataset has two parts: 1) a "header" that describes the data and includes things like the number of observations (NOBS) and 2) the data. The NOBS parameter pulls the number of observations off the header portion of the SAS data set and stores it into the variable named Last_Observation.

4. In the second SET statement, I use a POINT parameter. This parameter points to a particular record in SAS dataset without having to read through the rest of the dataset. By using the value stored in Last_Observation, I point directly to the last record in the SAS dataset. In so doing and in conjunction with the RENAME, I pull in the amount on the last record into Amount1.

I hope my explanation makes sense. Please ask questions.

Jim

Re: Creating a new column

Reeza — Wed, 23 Sep 2020 15:25:15 GMT

What are you trying to do here? Get a total or summary statistic to each row for another calculation is my guess? Is there another way to identify that last row besides the 'last row'?

If you're trying to add a summary stat, see alternative methods here:

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

@Nini1 wrote:

Hi everyone,

I am a new SAS user. I want a data with 2 columns 'ID' and 'amount' and want to create a new column 'amount1' that is the last observation's value in each line.

could you please tell me how can I do it?

Thank you in advance

ID amount amount1

12 2000 4000

167 2500 4000

256 1300 4000

3445 4000 4000

Re: Creating a new column

Nini1 — Mon, 28 Sep 2020 10:13:43 GMT

Thank you very much. Your replies helped me a lot.