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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

6 REPLIES 6
ballardw
Super User

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.

sai_ch
Obsidian | Level 7

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.

ballardw
Super User

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;
sai_ch
Obsidian | Level 7

Thanks for the solution ballardw

JediApprentice
Pyrite | Level 9

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?

ballardw
Super User

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;

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
  • 6 replies
  • 1156 views
  • 1 like
  • 3 in conversation