Help using Base SAS procedures

Iterating a step

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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: 9,662

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;

View solution in original post


All Replies
Super User
Posts: 17,750

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: 9,662

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: 9,662

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.

 

Thanks for your help in advance.

Super User
Posts: 9,662

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 433 views
  • 2 likes
  • 3 in conversation