topic Re: Copy first row value for all rows in SAS Programming

Copy first row value for all rows

PetePatel — Mon, 30 Mar 2020 09:22:36 GMT

Hi,

I have a dataset with variables date, AR and TA2.

I want to create AR2 and fix it at the earliest AR date for all subsequent rows (i.e. 2000-01-01). In this case the value should always be 0.020158103.

How can I achieve this in the shortest number of steps? I have tried the retain function as well as a sort, delete and merge but this is quite messy.

Date	AR	TA2	AR2
2000-01-01	0.020158103		0.020158103
2000-02-01	0.018692771	0.021258489	0.020158103
2000-03-01	0.021398338	0.022432956	0.020158103
2000-04-01		0.021881263	0.020158103
2000-05-01		0.022085627	0.020158103
2000-06-01		0.02190027	0.020158103
2000-07-01		0.021667115	0.020158103
2000-08-01		0.021301672	0.020158103

Re: Copy first row value for all rows

PeterClemmensen — Mon, 30 Mar 2020 09:25:52 GMT

Does this need to work for by groups as well?

Re: Copy first row value for all rows

PetePatel — Mon, 30 Mar 2020 09:27:11 GMT

Hi thanks for your reply draycut. No, it just needs to work for the earliest date which will always be the first observation as the dataset is sorted in ascending date order.

Re: Copy first row value for all rows

PeterClemmensen — Mon, 30 Mar 2020 09:34:30 GMT

Ok. then this will do

data have;
input Date :anydtdte12. AR TA2;
format date yymmdd10.;
infile datalines dlm=',';
datalines;
2000-01-01,0.020158103,0.020158103
2000-02-01,0.018692771,0.021258489
2000-03-01,0.021398338,0.022432956
2000-04-01,           ,0.021881263
2000-05-01,           ,0.022085627
2000-06-01,           ,0.02190027 
2000-07-01,           ,0.021667115
2000-08-01,           ,0.021301672
;

data want;
   set have;
   if _N_ = 1 then AR2 = AR;
   retain AR2;
run;

Re: Copy first row value for all rows

PetePatel — Mon, 30 Mar 2020 09:42:09 GMT

Thanks! I tried retain with first.date to cover myself but this works fine.