DATA Step, Macro, Functions and more

Expanding the number of observations

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Expanding the number of observations

Hello,

I am trying to expand the number of observations in a dataset based on certain conditions and am little stuck. I think it will be easier to explain through an example:

 

Have                                                                     Need

DateDenomNumer DateDenomNumer
1/1/200842 1/1/200811
4/1/200831 1/1/200810
7/1/200822 1/1/200811
10/1/200841 1/1/200810
    1/1/200810
    4/1/200810
    4/1/200811
    4/1/200810
    .  
    .  
    .  

 

So, I am expanding the first observation based on the value of Denom, i.e. 4, and randomly distributing the value of Numer, i.e. 2, across the four observations. Any help to solve this is greatly appreciated.

 

Thanks


Accepted Solutions
Solution
‎10-07-2016 04:39 PM
Super User
Posts: 11,343

Re: Expanding the number of observations

Try this:

data have;
   informat date mmddyy10. ;
   format date mmddyy10.;
   input Date Denom Numer;
datalines;
1/1/2008 4 2 
4/1/2008 3 1 
7/1/2008 2 2 
10/1/2008 4 1
;
run;

data want;
   set have (rename=(denom=d numer=n));
   do i = 1 to d;
      denom = 1;
      numer= (i le n);
      output;
   end;
   drop d n i;
run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Expanding the number of observations

Does your Need example happen to have an accidental extra record? Denom for the 1/1/2008 date is 4 but you show 5 records in the need.

 

I am somewhat interested in what you will do with the resulting data set. Are there other variables not mentioned involved in your actual problem? Otherwise I don't see why there would be any need for "random" distribution.

Contributor
Posts: 34

Re: Expanding the number of observations

Good observation ballardw, the need table should have only 4 observations. The distribution of Numer does not need to be "random". In the example given, the Numer for first two observations can be populated as 1 and the remaining 2 will have a value of 0.

Solution
‎10-07-2016 04:39 PM
Super User
Posts: 11,343

Re: Expanding the number of observations

Try this:

data have;
   informat date mmddyy10. ;
   format date mmddyy10.;
   input Date Denom Numer;
datalines;
1/1/2008 4 2 
4/1/2008 3 1 
7/1/2008 2 2 
10/1/2008 4 1
;
run;

data want;
   set have (rename=(denom=d numer=n));
   do i = 1 to d;
      denom = 1;
      numer= (i le n);
      output;
   end;
   drop d n i;
run;
Contributor
Posts: 34

Re: Expanding the number of observations

Thanks for the solution ballardw

Frequent Contributor
Posts: 123

Re: Expanding the number of observations

Hey @ballardw I'm curious to know exactly how this works. I understand the rename and you are essentially recreating the denom and numer variables but what does (i le n) mean? Does it mean set numer to i if it's less than or equal to n? I don't get how numer comes out as ones and then subsequent 0's? for the first date, on the second iteration of the do loop you would have 2 as i. And that would be less than or equal to 2(n) so why doesn't it output 2?

Super User
Posts: 11,343

Re: Expanding the number of observations

Posted in reply to JediApprentice

The code like:

 

Var = (a < b);

works with any logical comparison. SAS will return 1 for true and 0 for false.

Since the OP had wanted values of 1 and needed the count of ones to be equal to the Numer variable the comparison worked. Which is why I had asked about the "random order" which would have been much more complicated code.

 

In a similar way SAS treats any not-missing and non-zero value as "true" as revealed by this code:

Data _null_;
   do i = .,-5, 0.3, 0, 1, 12;
      if i then put "the value " i "is treated as True";
      else put "the value " i "is treated as False";
   end;
run;
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 387 views
  • 1 like
  • 3 in conversation