DATA Step, Macro, Functions and more

Do Loop or If Then?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Do Loop or If Then?

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:

Number of Obs       |  Month 1 |  Month 2 |  Month 3 |  Month 4 |  Month 5 |  Month 6 | ... | Month X|

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

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

 

Desired Output:

Number of Obs       |  Month 1 |  Month 2 |  Month 3 |  Month 4 |  Month 5 |  Month 6 | ... | Month X|

             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!!


Accepted Solutions
Solution
‎05-30-2017 05:12 PM
Super User
Posts: 5,083

Re: Do Loop or If Then?

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.

View solution in original post


All Replies
Solution
‎05-30-2017 05:12 PM
Super User
Posts: 5,083

Re: Do Loop or If Then?

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.

New Contributor
Posts: 2

Re: Do Loop or If Then?

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!

Super User
Posts: 6,939

Re: Do Loop or If Then?

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 151 views
  • 3 likes
  • 3 in conversation