Solved
New Contributor
Posts: 4

# Iterating a step

Time                     Variable 1        Variable 2

01JUN16:14:50:28 0.0407787557  .
01JUN16:14:50:29 0.0330219989  .
01JUN16:14:50:30 0.0366766247

01JUN16:14:50:30  .                    75
01JUN16:14:50:31 0.0366766247  .
01JUN16:14:50:32 0.0330219989  .
01JUN16:14:50:33 0.0354086933  .
01JUN16:14:50:33 .                     76
01JUN16:14:50:34 0.0393616559  .
01JUN16:14:50:35 0.0308341957  .
01JUN16:14:50:36 0.0393616559  .

I am hoping to get a columun that continues the Variable 2 value until it gets to the next number where it will then carry on this number. Ie this:

Time                     Variable 1        Variable 2 Solution

01JUN16:14:50:28 0.0407787557  .              74
01JUN16:14:50:29 0.0330219989  .              74
01JUN16:14:50:30 0.0366766247  .              74

01JUN16:14:50:30  .                    75            75
01JUN16:14:50:31 0.0366766247  .              75
01JUN16:14:50:32 0.0330219989  .              75
01JUN16:14:50:33 0.0354086933  .              75
01JUN16:14:50:33 .                     76            76
01JUN16:14:50:34 0.0393616559  .               76
01JUN16:14:50:35 0.0308341957  .               76
01JUN16:14:50:36 0.0393616559  .               76

Accepted Solutions
Solution
‎07-26-2016 05:37 PM
Super User
Posts: 10,787

## Re: Iterating a step

``````data have;
infile cards truncover expandtabs;
input Time : \$40.    Variable1        Variable2;
cards;
01JUN16:14:50:28 0.0407787557  .
01JUN16:14:50:29 0.0330219989  .
01JUN16:14:50:30 0.0366766247
01JUN16:14:50:30  .                    75
01JUN16:14:50:31 0.0366766247  .
01JUN16:14:50:32 0.0330219989  .
01JUN16:14:50:33 0.0354086933  .
01JUN16:14:50:33 .                     76
01JUN16:14:50:34 0.0393616559  .
01JUN16:14:50:35 0.0308341957  .
01JUN16:14:50:36 0.0393616559  .
;
run;

data want;
retain temp;
do until(not missing(Variable2) or last1);
set have end=last1;
end;

if not missing(Variable2) then temp=Variable2;
else flag=1;

do until(not missing(Variable2) or last2);
set have end=last2;
new=coalesce(Variable2,temp-1);
if flag then new=temp;
else new=coalesce(Variable2,temp-1);
output;
end;
drop temp flag Variable2;
run;``````

All Replies
Super User
Posts: 23,776

## Re: Iterating a step

Look at the RETAIN statement.

Retain solution;

if _n_=1 then solution = 74;

if variable2 ne . Then solution=variable2;

Solution
‎07-26-2016 05:37 PM
Super User
Posts: 10,787

## Re: Iterating a step

``````data have;
infile cards truncover expandtabs;
input Time : \$40.    Variable1        Variable2;
cards;
01JUN16:14:50:28 0.0407787557  .
01JUN16:14:50:29 0.0330219989  .
01JUN16:14:50:30 0.0366766247
01JUN16:14:50:30  .                    75
01JUN16:14:50:31 0.0366766247  .
01JUN16:14:50:32 0.0330219989  .
01JUN16:14:50:33 0.0354086933  .
01JUN16:14:50:33 .                     76
01JUN16:14:50:34 0.0393616559  .
01JUN16:14:50:35 0.0308341957  .
01JUN16:14:50:36 0.0393616559  .
;
run;

data want;
retain temp;
do until(not missing(Variable2) or last1);
set have end=last1;
end;

if not missing(Variable2) then temp=Variable2;
else flag=1;

do until(not missing(Variable2) or last2);
set have end=last2;
new=coalesce(Variable2,temp-1);
if flag then new=temp;
else new=coalesce(Variable2,temp-1);
output;
end;
drop temp flag Variable2;
run;``````
New Contributor
Posts: 4

## Re: Iterating a step

[ Edited ]

Thanks for that solution. It works perfectly except my data set goes back to 1 after each day (Variable2). The data below shows how the last  number in the Variable2 column doesn't get retained but goes to 0.

Date           Date_Time           Variable1       Variable2

20JUL2016 20JUL16:00:19:26 0.0509201597 124
20JUL2016 20JUL16:00:19:27 0.054355847 124
20JUL2016 20JUL16:00:19:28 0.051141817 124
20JUL2016 20JUL16:00:19:29 0.0469164758 124
.                20JUL16:00:19:29 .                    125
20JUL2016 20JUL16:00:19:30 0.0441457602 0
20JUL2016 20JUL16:00:19:31 0.0492992911 0
20JUL2016 20JUL16:00:19:32 0.0641364728 0
20JUL2016 20JUL16:00:19:33 0.0691930286 0
20JUL2016 20JUL16:00:19:34 0.0612133679 0
20JUL2016 20JUL16:00:19:35 0.0720607192 0

Any idea on how to fix this?

In addition, do you have any code that will sum Variable1 for each Variable2 and Date.

Ie

20Jul2016 1 10.5

20Jul2016 2 11

20Jul2016 3  7

.....

21Jul2016 1  8.5

etc

Super User
Posts: 10,787

## Re: Iterating a step

```Your data does not look like what you showed before, there are not any missing value .

data have;
infile cards truncover expandtabs;
input Date : \$20.   Date_Time  : \$40.    Variable1        Variable2;
cards;
0JUL2016 20JUL16:00:19:26   0.0509201597 124
20JUL2016 20JUL16:00:19:27   0.054355847 124
20JUL2016 20JUL16:00:19:28   0.051141817 124
20JUL2016 20JUL16:00:19:29   0.0469164758 124
.         20JUL16:00:19:29    .            125
20JUL2016 20JUL16:00:19:30   0.0441457602 .
20JUL2016 20JUL16:00:19:31   0.0492992911 .
20JUL2016 20JUL16:00:19:32   0.0641364728 .
20JUL2016 20JUL16:00:19:33   0.0691930286 .
20JUL2016 20JUL16:00:19:34   0.0612133679 .
20JUL2016 20JUL16:00:19:35   0.0720607192 .
;
run;

data want;
retain temp;
do until(not missing(Variable2) or last1);
set have end=last1;
end;

if not missing(Variable2) then temp=Variable2;
else flag=1;

do until(not missing(Variable2) or last2);
set have end=last2;
if flag then new=temp;
else new=coalesce(Variable2,temp-1);
output;
end;
drop temp flag Variable2;
run;

OUTPUT:
Before
Date	Date_Time	Variable1	Variable2
0JUL2016	20JUL16:00:19:26	0.050920	124
20JUL2016	20JUL16:00:19:27	0.054356	124
20JUL2016	20JUL16:00:19:28	0.051142	124
20JUL2016	20JUL16:00:19:29	0.046916	124
20JUL16:00:19:29	.	125
20JUL2016	20JUL16:00:19:30	0.044146	.
20JUL2016	20JUL16:00:19:31	0.049299	.
20JUL2016	20JUL16:00:19:32	0.064136	.
20JUL2016	20JUL16:00:19:33	0.069193	.
20JUL2016	20JUL16:00:19:34	0.061213	.
20JUL2016	20JUL16:00:19:35	0.072061	.
After
Date	Date_Time	Variable1	new
0JUL2016	20JUL16:00:19:26	0.050920	124
20JUL2016	20JUL16:00:19:27	0.054356	124
20JUL2016	20JUL16:00:19:28	0.051142	124
20JUL2016	20JUL16:00:19:29	0.046916	124
20JUL16:00:19:29	.	125
20JUL2016	20JUL16:00:19:30	0.044146	125
20JUL2016	20JUL16:00:19:31	0.049299	125
20JUL2016	20JUL16:00:19:32	0.064136	125
20JUL2016	20JUL16:00:19:33	0.069193	125
20JUL2016	20JUL16:00:19:34	0.061213	125
20JUL2016	20JUL16:00:19:35	0.072061	125

In addition, do you have any code that will sum Variable1 for each Variable2 and Date.
Ie
20Jul2016 1 10.5
20Jul2016 2 11
20Jul2016 3  7
.....
21Jul2016 1  8.5

I don't understand what you mean. Where is 1,2,3,..10.5,11,7..   coming from ?

```
New Contributor
Posts: 4

## Re: Iterating a step

I have uploaded a larger data set that will help with my question.

Basically I have data for Variable1 that is measured every second. The actual data comes from a belt weigher. This belt weigher feed a truck, column 'new'. I am looking to obtain data that sums Variable1 for each truck. Ideally I want a dataset that gives me tonnes/truck over each day.

Super User
Posts: 10,787

## Re: Iterating a step

```Sorry. Still can't get your point . Maybe you need this :

data have;
infile cards truncover expandtabs;
input Date : \$20.   Date_Time  : \$40.    Variable1        Variable2;
cards;
0JUL2016 20JUL16:00:19:26   0.0509201597 124
20JUL2016 20JUL16:00:19:27   0.054355847 124
20JUL2016 20JUL16:00:19:28   0.051141817 124
20JUL2016 20JUL16:00:19:29   0.0469164758 124
.         20JUL16:00:19:29    .            125
20JUL2016 20JUL16:00:19:30   0.0441457602 .
20JUL2016 20JUL16:00:19:31   0.0492992911 .
20JUL2016 20JUL16:00:19:32   0.0641364728 .
20JUL2016 20JUL16:00:19:33   0.0691930286 .
20JUL2016 20JUL16:00:19:34   0.0612133679 .
20JUL2016 20JUL16:00:19:35   0.0720607192 .
;
run;

data want;
retain temp;
do until(not missing(Variable2) or last1);
set have end=last1;
end;

if not missing(Variable2) then temp=Variable2;
else flag=1;

do until(not missing(Variable2) or last2);
set have end=last2;
if flag then new=temp;
else new=coalesce(Variable2,temp-1);
output;
end;
drop temp flag ;
run;

proc means data=want noprint;
by Date new notsorted;
var Variable1;
output out=final_want sum=sum_Variable1;
run;

```
New Contributor
Posts: 4

## Re: Iterating a step

That worked exactly! Thankyou very much

☑ This topic is solved.