Help using Base SAS procedures

Retain statement questions

Reply
Super Contributor
Posts: 259

Retain statement questions

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

SectionPeriodCount
1Q11
1Q2.
1Q3.
1Q42
1Q5.
2Q1.
2Q21
2Q3.
3Q11

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

SectionPeriodCount
1Q11
1Q21
1Q31
1Q42
1Q52
2Q10
2Q21
2Q31
3Q11
...
Super User
Super User
Posts: 7,955

Re: Retain statement questions

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.

Super User
Posts: 7,796

Re: Retain statement questions

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,955

Re: Retain statement questions

Posted in reply to KurtBremser

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.

Respected Advisor
Posts: 3,799

Re: Retain statement questions

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;

Capture.PNG
Ask a Question
Discussion stats
  • 4 replies
  • 337 views
  • 0 likes
  • 4 in conversation