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 |
... |
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.
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
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.
Looks like a common LOCF problem to me.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.