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

- 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
- RSS Feed
- Permalink
- 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
- RSS Feed
- Permalink
- 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
- RSS Feed
- Permalink
- 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
- RSS Feed
- Permalink
- 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
- RSS Feed
- Permalink
- 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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code