turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Do Loop or If Then?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-30-2017 04:49 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to thrice1984

05-30-2017 05:01 PM

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.

All Replies

Solution

05-30-2017
05:12 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to thrice1984

05-30-2017 05:01 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

05-30-2017 05:13 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to thrice1984

05-31-2017 04:06 AM

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

Maxims of Maximally Efficient SAS Programmers