BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
John3
Calcite | Level 5

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

7 REPLIES 7
Reeza
Super User

Look at the RETAIN statement. 

 

Retain solution;

if _n_=1 then solution = 74;

if variable2 ne . Then solution=variable2;

Ksharp
Super User
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;
John3
Calcite | Level 5

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

Ksharp
Super User
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 ?

John3
Calcite | Level 5

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.

Ksharp
Super User
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;


John3
Calcite | Level 5

That worked exactly! Thankyou very much

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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