topic Re: Create a column of repeated values based on the earliest value of another column in SAS Programming

Create a column of repeated values based on the earliest value of another column

davkav9 — Tue, 30 Jul 2019 10:21:09 GMT

I'm sure there's a simple solution to this problem, but I'm a new enough user so here goes. I'm using SAS enterprise guide. I have a dataset like so

var1 date

1.1 Feb2018

1.4 Mar2018

2.6 Apr2018

3.3 May2018

4.5 Jun2018

and I would like to create a column that repeats the earliest var1 value, like so

var1 date base

1.1 Feb2018 1.1

1.4 Mar2018 1.1

2.6 Apr2018 1.1

3.3 May2018 1.1

4.5 Jun2018 1.1

Not sure if I should use proc sql or a data step here. Thanks in advance

Re: Create a column of repeated values based on the earliest value of another column

PrestickNinja — Tue, 30 Jul 2019 11:37:44 GMT

Assuming the data is sorted by var, you should be able to use a retain statement?

For example:

data temp;
	set vardate;
	retain base;
	if _N_ = 1 then base = var;
run;

You can expand this to grouped data (for example if you had another column ahead) using "BY" and "first." statement.

Re: Create a column of repeated values based on the earliest value of another column

davkav9 — Tue, 30 Jul 2019 11:43:17 GMT

Perfect, exactly what I wanted. Thank you!

Re: Create a column of repeated values based on the earliest value of another column

novinosrin — Tue, 30 Jul 2019 12:17:06 GMT


  
data have;
input var1 date : anydtdte7.;
format date monyy7.;
cards;
1.1  Feb2018
1.4  Mar2018
2.6  Apr2018
3.3  May2018
4.5  Jun2018
;


proc sql;
create table want as
select *,(select var1  from have  having date=min(date)) as base
from have;
quit;