topic Re: Generating a new variable... do loop? in SAS Programming

Generating a new variable... do loop?

katiexyz — Thu, 01 Nov 2012 09:32:56 GMT

Hello,

I am a SAS amateur, so forgive me if this is a silly Q.

I need to generate a variable "type" which is...

...etc

So 111 222 333 repeated, until a specific N. (Say N=45)

How can I do this without manually typing it in? I guess a Do Loop? So far I have N=_N_ for the observation numbers to be a variable N, and I guess I should do some sort of equation for Type=...(something to do with N like modulo) ?

I have tried this and got no where. Also, every time I manage some form of do loop, the numbers 111222333 are next to each N, making 9 times as many data rows!

So stuck.

Can anyone help?

Re: Generating a new variable... do loop?

RichardinOz — Thu, 01 Nov 2012 11:17:45 GMT

Data want ;

Do repeat = 1 to 45 ;

Do type = 1 to 3 ;

Do times = 1 to 3 ;

Output ;

End ;

Drop repeat times ;

Run ;

Richard in Oz

Re: Generating a new variable... do loop?

RichardinOz — Thu, 01 Nov 2012 11:24:17 GMT

Hope the above is what you want. The first loop sets the overall number of pattern repeats, the second cycles the value of type, and the third duplicated the type value 3 times. The key statement is OUTPUT, which controls when the datastep pushes a value to the output table.

You could make the number of repeats driven by a macro variable

%Let control = 45 ;

Data want ;

Do repeat = 1 to &control ;

etc

Richard in Oz

Re: Generating a new variable... do loop?

SteveDenham — Thu, 01 Nov 2012 11:30:22 GMT

A couple of questions before I suggest anything. First, is N always a multiple of 9, with 3 levels of 3 each? If so, then the following might work:

/* Make up some data */

data one;
do i = 1 to 45;
output;
end;
run;

data two;
set one;

k=mod(_n_,9); /* This depends on the question I asked - Does the pattern always depend on three sets of three? */
if k=0 then k1=9;
else k1=k; /* Sets the values with remainder=0 to remainder=9 */
kceil=ceil(k1/3); /* Generates what you need */
run;

I am sure that those who are better programmers than me will come up with something more general, but this may get you started. The second datastep is key, and could be applied to any

dataset.

Steve Denham

Message was edited by: Steve Denham

Re: Generating a new variable... do loop?

katiexyz — Thu, 01 Nov 2012 11:41:57 GMT

Thanks Richard and Steve!

I am not down with macros yet but both of the other methods worked!

Thanks for your help

Re: Generating a new variable... do loop?

Haikuo — Thu, 01 Nov 2012 14:03:35 GMT

Ok, the following constructs may give you more control over what you expect to have:

/* Option1: if you want the same pattern repeats until the total obs reach 45*/

data want1;

do type=1 to 3;

do i=1 to 3;

output;

n+1;

if n>=45 then stop;

end;

if type=3 then type=0;

end;

run;

/*Option2: same pattern repeats until the number of 3x1 blocks reaches 45*/

data want2;

do type=1 to 3;

do i=1 to 3;

output;

end;

if type=3 then type=0;

n+1;

if n>=45 then stop;

end;

run;

/*Option3: Same pattern repeats until the number of 3x3 blocks reaches 45, same as Richard's suggestion*/

data want3;

do type=1 to 3;

do i=1 to 3;

output;

end;

if type=3 then do; n+1;type=0;end;

if n>=45 then stop;

end;

run;

The key part of the code is: "if type=3 then type=0;", that makes you an infinite loop; then depending on where you insert your counter and switch, you can get different outcomes.

Good luck!

Haikuo

Re: Generating a new variable... do loop?

katiexyz — Thu, 01 Nov 2012 16:26:51 GMT

I don't know what has happened... i changed a variable name, and now when i run, i am getting 18255 data rows

Re: Generating a new variable... do loop?

Haikuo — Thu, 01 Nov 2012 16:48:46 GMT

Let's see your code if you don't mind.

Haikuo

Re: Generating a new variable... do loop?

katiexyz — Thu, 01 Nov 2012 16:59:26 GMT

data work2;

set work1;

n=_n_;

format type typeformat. intermediate intermediateformat.;

then

input type;cards; blahblah...

or paste your code in.

when i proc print work1 it prints perfectly. and in work2 i am only adding the variable "type", so it must be something here.

Re: Generating a new variable... do loop?

art297 — Thu, 01 Nov 2012 17:06:49 GMT

That doesn't appear to be a complete program .. at least not one that would run. Post both sets of the code that you actually ran for creating work1 and work2, as well as your log.

Re: Generating a new variable... do loop?

Haikuo — Thu, 01 Nov 2012 17:09:28 GMT

I am confused here, are you saying you use 'set' statement along with 'input, cards'? What is purpose of that? how do you get 'work1'? code please?

Haikuo

Re: Generating a new variable... do loop?

RichardinOz — Fri, 02 Nov 2012 00:10:17 GMT

If you already have a dataset and just want to add the type column, here is a solution. Note that the CEIL() function is like INT() but rounds up to the next integer. The MOD() function is the remainder when you do integer division. This will work in the pattern you want for however many rows you have.

Data want ;

set have ;

type = 1 + mod(ceil(_n_/3)-1, 3) ;

Run ;

Richard in Oz

Re: Generating a new variable... do loop?

katiexyz — Fri, 02 Nov 2012 08:41:27 GMT

ods graphics on;
proc format;
value typeformat 1="X1" 2="X2" 3="X3" 4="X4";
value $styleformat a="Y1" b="Y2" c="Y3" d="Y4" e="Y5";
run;

data work;
input style$ r1-r12;
cards;
a 1 2 3 4 5 6 7 8 9 10 11 12
b 1 2 3 4 5 6 7 8 9 10 11 12
c 1 2 3 4 5 6 7 8 9 10 11 12
d 1 2 3 4 5 6 7 8 9 10 11 12
e 1 2 3 4 5 6 7 8 9 10 11 12
;
proc transpose data=work out=work1 (rename=(col1=results));
by style;

data work2;

set work1;
n=_n_;
format type typeformat. style styleformat.;
Do repeat = 1 to 60 ;
Do type = 1 to 4 ;
Do times = 1 to 3 ;
Output ;
End ;
End ;
End ;
Drop _name_;
Run ;
proc print data=work2;
run;

This is where N=60, not 45. And using Richard's earlier method.

Okay I changed the data and the names obviously because this is work stuff. I was given data which was:

factor:"style" which has 5 levels a,b,c,d,e

factor:"type" which has 4 levels 1,2,3,4

And there are 3 repeats of each combination

1 2 3 4

a [1 2 3][ 4 5 6] [7 8 9] [10 11 12]

b [1 2 3][ 4 5 6] [7 8 9] [10 11 12]

c [1 2 3][ 4 5 6] [7 8 9] [10 11 12]

d [1 2 3][ 4 5 6] [7 8 9] [10 11 12]

e [1 2 3][ 4 5 6] [7 8 9] [10 11 12]

So that's what data I was given, I input style and the results, transposed it, added "type".

Wrong?

Re: Generating a new variable... do loop?

Haikuo — Fri, 02 Nov 2012 13:35:24 GMT

Wrong? Well, your code runs fine, it will give you 3*4*60*60=43200 obs as being expected. For every obs from work1, you will go through 3*4*60 cycle with output. What you need here is not a regular loop. You need a loop call DOW (try google it to learn): and yes, _n_=1 all the time. if you need a counter , replace n=_n_ with n+1; although it seems harmless, I don't know how you set your 'repeat' up limit as '60', it is actually 60/4/3=5. You can get it before hand by using nobs= ; I have also moved your drop statement to incoming data set, for better efficiency.

data work2;

Do repeat = 1 to nobs/4/3 ;

Do type = 1 to 4 ;

Do times = 1 to 3 ;

set work1 (drop=_name_) nobs=nobs;

n=_n_; /*n+1;*/

format type typeformat. style styleformat.;

Output ;

End ;

/*Drop _name_;*/

Run ;

Haikuo

Update: After going through this thread one more time, it turns out that Richard has already provided a solid solution, if you only need 'type':

data work2;

set work1;

type=1+mod(ceil(_n_/4)-1,4);

run;