topic Re: Iterating a step in SAS Procedures

Iterating a step

John3 — Thu, 21 Jul 2016 03:07:11 GMT

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

Re: Iterating a step

Reeza — Thu, 21 Jul 2016 04:15:56 GMT

Look at the RETAIN statement.

Retain solution;

if _n_=1 then solution = 74;

if variable2 ne . Then solution=variable2;

Re: Iterating a step

Ksharp — Thu, 21 Jul 2016 05:55:04 GMT

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;

Re: Iterating a step

John3 — Thu, 28 Jul 2016 01:33:31 GMT

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.

20Jul2016 1 10.5

20Jul2016 2 11

20Jul2016 3 7

.....

21Jul2016 1 8.5

etc

Re: Iterating a step

Ksharp — Thu, 28 Jul 2016 02:11:03 GMT

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 ?

Re: Iterating a step

John3 — Thu, 28 Jul 2016 02:27:05 GMT

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.

Thanks for your help in advance.

Re: Iterating a step

Ksharp — Thu, 28 Jul 2016 03:40:44 GMT

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;

Re: Iterating a step

John3 — Thu, 28 Jul 2016 03:45:56 GMT

That worked exactly! Thankyou very much