BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thrice1984
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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.

thrice1984
Calcite | Level 5

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!

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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