Desktop productivity for business analysts and programmers

How to create duplicate rows based on a number in a cell??

Reply
New Contributor
Posts: 2

How to create duplicate rows based on a number in a cell??

So I have a data set that will end up being huge when all said and done...however I am trying to figure out how to easily create a type of "waterfall effect" with this data table. 

I have a list of accts and have already calculated the number of months between to dates, number will be anywhere between 0 and 124 and this number identifies how many times this record (row of data) needs to be repeated in the data table.  previously before we had this many months of data we just created a query for each origination month and was able to append those queries in the end...etc.  But I am trying to do this a more effecient and easilyrepeatable way......any help or suggestions on how to accomplish this would be great!!

Regular Contributor
Posts: 229

Re: How to create duplicate rows based on a number in a cell??

Please provide a small sample of data you have and output you want. So that someone will able to easily intrepret, what you are trying to say

New Contributor
Posts: 2

Re: How to create duplicate rows based on a number in a cell??

The data is confidential and I cannot post actual data here but I can try and give you an example......SEE ATTACHED

Grand Advisor
Posts: 10,211

Re: How to create duplicate rows based on a number in a cell??

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Any example data that duplicates the behavior is fine.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Spreadsheets have "cells". SAS data sets have variables and observations. A "cell" becomes the value of a variable for a given observation (row).

 

A generic approach looks like this:

 

data want;

   set have;

   if variable >0 then do i =1 to round(variable,1);

      output;

   end;

   drop i;

run;

 

The if is because you would need to ensure an actual value and positive. The round is because we don't know if you have fraction values. If your variable has a value of 2.1 how many duplicate rows do you want for the 0.1 portion.

 

Ask a Question
Discussion stats
  • 3 replies
  • 112 views
  • 2 likes
  • 3 in conversation