topic Re: Do Loop or If Then? in SAS Programming

Do Loop or If Then?

thrice1984 — Tue, 30 May 2017 20:49:44 GMT

Hi!

First time poster, so hello everyone!

I have a data step that I am having a very difficult time with. I have a data set that looks like this:

Data Set:

4 | 50 | 25 | . | 10 | . | . |

5 | 50 | 25 | . | . | 10 | . |

Desired Output:

4 | 50 | 25 | 0 | 10 | . | . |

5 | 50 | 25 | 0 | 0 | 10 | . |

Basically, for every row:

If the Month is less than the Number of Obs, I need to impute a 0 and if the Month is greater than the Number of Obs, I need to leave the value missing.
- So, in the first row, I need to drop in a 0 in Month 3 and leave Months 5 - Months X untouched.

In Excel, I could put in a formula that says if (Month1 <= Number of Obs, Number, 0) and drag the formula but in SAS I cannot get this to work.

Does anyone know how to do this? I am driving myself crazy over this, so any help would be appreciated. I can't hard code this all manually because I will have about a hundred months of columns.

Thanks!!

Re: Do Loop or If Then?

Astounding — Tue, 30 May 2017 21:01:13 GMT

Since you already have a SAS data set, I have to assume you already know how many months you have. It's a simple application for arrays (if anything using arrays can actually be considered simple):

data want;

set have;

array months {60} month1-month60;

do i=1 to number_of_obs;

if months{i}=. then months{i}=0;

end;

run;

I'm using 60 as the total number of months, but you have to plug in the right number there.

Re: Do Loop or If Then?

thrice1984 — Tue, 30 May 2017 21:13:46 GMT

That worked perfectly, thank you so much for the time. I'm sorry if that was a stupid question. Your solution was very elegant and I appreciate that greatly!

Re: Do Loop or If Then?

Kurt_Bremser — Wed, 31 May 2017 08:06:49 GMT

A question can only be stupid if it is stated in a stupid way. Your question gave a clear picture of your situation, and what you wanted to achieve, so - voila! - @Astounding was able to quickly come up with a solution for you. So your question was a good question typical for a beginner, and it was one of those that are a pleasure to answer. Such questions can never be considered "stupid"!

Just one hint for the future: it helps us greatly when example data is provided in a way that makes it easy for us to recreate your datasets quickly and independent from the environment we use. So we like to present data in a data step, which might have looked like that in your case:

data have;
input number_of_obs month1 month2 month3 month4 month5 month6;
cards;
4 50 25 . 10 . .
5 50 25 . . 10 .
;
run;

Just mastering the creation of a dataset with a data step like this provides a learning experience on its own (it was for me).