topic Retain statement questions in SAS Procedures

Retain statement questions

brophymj — Wed, 12 Aug 2015 09:26:40 GMT

I am looking to convert a table like the one below...

Section	Period	Count
1	Q1	1
1	Q2	.
1	Q3	.
1	Q4	2
1	Q5	.
2	Q1	.
2	Q2	1
2	Q3	.
3	Q1	1

into the following table. So, turn the count into a running total for each section.

Section	Period	Count
1	Q1	1
1	Q2	1
1	Q3	1
1	Q4	2
1	Q5	2
2	Q1	0
2	Q2	1
2	Q3	1
3	Q1	1
	...

Re: Retain statement questions

RW9 — Wed, 12 Aug 2015 09:37:58 GMT

Hi,

Can you provide some test data in the form of a datastep, as your data is before. I don't see why you only have number at certain points. I mean you could do retain, however, why do you only have some numbers, it may be easier to change the way count is added to the dataset rather than retain them afterwards.

Re: Retain statement questions

Kurt_Bremser — Wed, 12 Aug 2015 10:21:30 GMT

data want (drop=oldcount);

set have (rename=(count=oldcount));

by section;

retain count;

if first.section then count = 0;

if oldcount ne . then count=oldcount;

run;

Edit: Replaced zero with dot in second if condition

Post updated: Kurt Bremser

Re: Retain statement questions

RW9 — Wed, 12 Aug 2015 10:33:22 GMT

Yes, but it still begs the question, what is the count column, how is it arrived at, i.e. why does Q1 have 1 and Q2 not? Also, you can't have a Q5.

Re: Retain statement questions

data_null__ — Wed, 12 Aug 2015 11:42:23 GMT

Looks like a common LOCF problem to me.

data section;
   infile cards expandtabs; 
   input section period :$2. count;
   cards; 
1  Q1 1
1  Q2 .
1  Q3 .
1  Q4 2
1  Q5 .
2  Q1 .
2  Q2 1
2  Q3 .
3  Q1 1
;;;;
   run; 
proc print; 
   run; 
data section;
   update section(obs=0 keep=section) section;
   by section;
   if first.section then count=coalesce(count,0);
   output; 
   run; 
proc print; 
   run;