BookmarkSubscribeRSS Feed
katiexyz
Calcite | Level 5

Hello,

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

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

1

1

1

2

2

2

3

3

3

1

1

1

2

2

...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? 

13 REPLIES 13
RichardinOz
Quartz | Level 8

Data want ;

    Do repeat = 1 to 45 ;

        Do type = 1 to 3 ;

            Do times = 1 to 3 ;

                Output ;

            End ;

        End ;

    End ;

    Drop repeat times ;

Run ;

Richard in Oz

RichardinOz
Quartz | Level 8

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

katiexyz
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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

Haikuo

katiexyz
Calcite | Level 5

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. Smiley Sad

art297
Opal | Level 21

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.

Haikuo
Onyx | Level 15

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

katiexyz
Calcite | Level 5

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? Smiley Sad

RichardinOz
Quartz | Level 8

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

SteveDenham
Jade | Level 19

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

katiexyz
Calcite | Level 5

Thanks  Richard and Steve!

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

Thanks for your help Smiley Happy

Haikuo
Onyx | Level 15

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

Haikuo
Onyx | Level 15

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 ;

End ;

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;

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
  • 13 replies
  • 1892 views
  • 0 likes
  • 5 in conversation