topic Re: Multiply a column n times in SAS Programming

Multiply a column n times

iscgonzalez — Fri, 09 Feb 2018 17:37:55 GMT

Hello guys,

I'm kinda stuck here, hoping someone can give me some light...

I have a column with a string that represents status on transactions on a certain period (monthly), something like this:

data test;
input status $50.;
datalines;
02010103
01020301
;
run;

Each period is two characters lenght, then I have to split the string onto multiple columns, and repeat those columns 4 times so I have weeks instead of months) something like this:

w1 w2 w3 w4 w5 w6.....

--------------------------------

02 02 02 02 01 01 ...

01 01 01 01 02 02 ....

This is causing me a headache, because I can think in a way to split, but no way to make columns names be in order. Any idea?

Re: Multiply a column n times

Reeza — Fri, 09 Feb 2018 17:43:48 GMT

What's the exact output you'd get from the two entries you have below?

@iscgonzalez wrote:

Hello guys,

I'm kinda stuck here, hoping someone can give me some light...

I have a column with a string that represents status on transactions on a certain period (monthly), something like this:
data test;
input status $50.;
datalines;
02010103
01020301
;
run;
Each period is two characters lenght, then I have to split the string onto multiple columns, and repeat those columns 4 times so I have weeks instead of months) something like this:

w1 w2 w3 w4 w5 w6.....

--------------------------------

02 02 02 02 01 01 ...

01 01 01 01 02 02 ....

This is causing me a headache, because I can think in a way to split, but no way to make columns names be in order. Any idea?

Re: Multiply a column n times

iscgonzalez — Fri, 09 Feb 2018 17:50:10 GMT

I'm expecting something like this

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01

Re: Multiply a column n times

Reeza — Fri, 09 Feb 2018 17:51:39 GMT

So each value is always repeated 4 times?

Are you reading this data from a text file or are you starting from a SAS data set of sorts?

@iscgonzalez wrote:

I'm expecting something like this

w1 w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 w14 w15 w16

02 02 02 02 01 01 01 01 01 01 01 01 03 03 03 03

01 01 01 01 02 02 02 02 03 03 03 03 01 01 01 01

Re: Multiply a column n times

novinosrin — Fri, 09 Feb 2018 17:52:32 GMT

something like this.....i'm afraid doesn't help.

1. does all your string in every record have a length of 8 chars or perhaps the length an even number and are equal?

2. if yes, are you expecting 16 columns for each record?

3. are you reading a sas dataset or external file?

Re: Multiply a column n times

novinosrin — Fri, 09 Feb 2018 18:02:33 GMT

will this help?

data test;
input status $50.;
datalines;
02010103
01020301
;
run;

data _have;
set test;
do _n_=1 to length(status) by 2;
 do j=1 to 4;
temp=substr(status, _n_, 2);
output;
end;
end;
keep status  temp;
run;

proc transpose data=_have out=want(drop=_name_);
by status notsorted;
var temp;
run;

Re: Multiply a column n times

iscgonzalez — Fri, 09 Feb 2018 18:03:10 GMT

Yes, each value has to be repeated 4 times. And I'm reading from a existing dataset.

In this case not every record has the same size, but all of them have even number of characters, so each period is represented by 2 characters. I think that is not he difficult part, because I can calculate the number of characters, divide them by 2 and get the number of periods and in that way try to limit the loop iterations.

The main objective would be to repeat each value (2 characters) 4 times.

Re: Multiply a column n times

novinosrin — Fri, 09 Feb 2018 18:04:12 GMT

i have answered below. Please take a look. Thank you!

Re: Multiply a column n times

ballardw — Fri, 09 Feb 2018 18:15:40 GMT

If those values relate to time in any way then you need to consider very much how your "week" is defined and the relationship to "month".

For instance Jan 2018 has 5 Mondays. So should that be 5 weeks or 4? If you are using Sundays as a week start then July 2018 has 5 Sundays.

Also does the length of status vary? This becomes a code maintenance problem if you process data today with a length of 8 and 4 periods( 02 01 01 03 for the first record) and in the future you have additional data for more periods ( 02 01 01 03 01 02 for example) your later data set would have more variables requiring you to modify all of your code to process though additional variables.

The length of your status variable implies that you might have as many as 25 of these two digit periods. You might find it easier in the long run to generate a "long" data set with a week indicator in a separate variable. (which would also solve the "how to generate names for 25*4 variables).

Something like this perhaps:

data test;
input status $50.;
/* assumes whatever the 02 and such represent are always 2 characters*/
periods= length(status)/2;
week=0;
length code $ 2.;
do i=1 to periods;
   code = substr(status,2*i-1,2);
   do j=1 to 4;
      week+1;
      output;
   end;
end;
drop periods i j;
datalines;
02010103
01020301
;
run;

Re: Multiply a column n times

Reeza — Fri, 09 Feb 2018 18:18:31 GMT

data test;
input status $50.;
datalines;
02010103
01020301
;
run;


data want;
    set test;
    *CREATE ARRAY, 25 WEEKS BASED ON LENGTH 25 CHARS*4 = 100 MAX;
    array wk(*) $2. wk1-wk100;

    *determine the number of iterations;
    n_iter = length(status)/2;

    do i=1 to n_iter;
    
        *take the substr desired;
        Value = substr(status, (i-1)*2 + 1, 2);

        *assign to wk, could also write a small do loop here but not super efficient;
        wk((I-1)*4+1)=value;
        wk((I-1)*4+2) = value;
        wk((I-1)*4+3) = value;
        wk((I-1)*4+4) = value;


    end;
run;

Re: Multiply a column n times

iscgonzalez — Fri, 09 Feb 2018 18:29:31 GMT

Thank you very much!!! That was exactly what I needed

Re: Multiply a column n times

Ksharp — Sat, 10 Feb 2018 11:25:05 GMT

data test;
input status $50.;
datalines;
02010103
01020301
;
run;

data temp;
 set test;
 n+1;
 temp=prxchange('s/(\d\d)/$1 /',-1,status);
 do i=1 to countw(temp);
  value=scan(temp,i);
  do j=1 to 4;
    output;
  end;
 end;
 keep n value;
run;
proc transpose data=temp out=want prefix=w;
 by n;
 var value;
run;
proc print noobs;run;

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	w11	w12	w13	w14	w15	w16
02	02	02	02	01	01	01	01	01	01	01	01	03	03	03	03
01	01	01	01	02	02	02	02	03	03	03	03	01	01	01	01