topic Re: Repeating Values in New Column in SAS Programming

Repeating Values in New Column

SNG1 — Wed, 21 Aug 2019 21:15:17 GMT

Hi All,

I am trying to create a SAS data set which already has two columns with various data, what I need is to create another column which repeats certain numbers (that I can specify) and these repeat a certain number of time (which I can specify).

For Example

Column A & B denotes random data, looking for a way to create column C
Repeat 1 3 times, repeat 2 2 times, repeat 3 4 times, etc.

A	B	C
3456	234	1
4563456	3566	1
456456	145345	1
6876	7567	2
3554	345435	2
32454	8769	3
13135	345	3
45647	45345	3
65923	345353	3

I need to be able to specify the number I see in column C (i.e. 1,2,3) as well as the number of times I want them to appear (i.e. 1 to appear 3 times, 2 to appear 2 times and 3 to appear 4 times)

So essentially repeating values in a new column.

Any help is much appreciated

Re: Repeating Values in New Column

Reeza — Wed, 21 Aug 2019 21:35:37 GMT

I would probably provide the number and repeats in a data set and then use SAS to expand it and merge it in.

You would need to modify the lines in the data repeat data set.

data repeat;
input number times;
do i=1 to times;
output;
end;
cards;
1 3
2 2
3 4
;;;;

data want;
merge have repeat;
run;

Re: Repeating Values in New Column

hashman — Wed, 21 Aug 2019 22:46:07 GMT

@SNG1:

If I understand your spec correctly (and it's a big IF), you could do this:

data have ;           
  input A B ;         
  cards ;             
    3456    234       
 4563456   3566       
  456456 145345       
    6876   7567       
    3554 345435       
   32454   8769       
   13135    345       
   45647  45345       
   65923 345353       
run ;                 
                      
data spec (keep = C) ;
  input C N ;         
  do _n_ = 1 to N ;   
    output ;          
  end ;               
  cards ;             
1 3                   
2 2                   
3 4                   
run ;                 
                      
data want  ;          
  merge have spec ;   
run ;

If your spec is not exhaustive - i.e., the total of N in SPEC is less than the number of records in HAVE , - the non-covered slack of the output records will receive a missing value for C.

Alternatively, you can provide a spec using an array and do it this way:

data want ;                           
  array spec [3] _temporary_ (3 2 4) ;
  do C = 1 to dim (spec) ;            
    do _n_ = 1 to spec [C] ;          
      set have ;                      
      output ;                        
    end ;                             
  end ;                               
run ;

However, it works a bit differently if your spec is not exhaustive: Instead of padding the non-covered slack with C=., once the spec values have been used up, it will start assigning C using the same spec from the first item on. It can be viewed as an advantage or not be desirable - depending on what you need to do in the case of non-exhaustive spec.

Kind regards

Paul D.

Re: Repeating Values in New Column

SNG1 — Thu, 22 Aug 2019 12:29:35 GMT

Thanks a Lot for the Replies both