SAS Code Help: Need to create a column that has multiple values and assign all values to each row

[Pattyp33]

Hi!

First time poster so please excuse anything I miss.

I work with confidential data so I won't be able to post the details here but essentially what I'm trying to do is create one column that has multiple variables (a scale if you will) and assign that scale to each observation.

Table 1 looks like this

Account	Account Number
Alpha	ABC1
Beta	ABC2
Delta	ABC3

And I want the output to look like this:

Account	Account Number	Scale
Alpha	ABC1	1
Alpha	ABC1	2
Alpha	ABC1	3
Beta	ABC2	1
Beta	ABC2	2
Beta	ABC2	3
Delta	ABC3	1
Delta	ABC3	2
Delta	ABC3	3

Any advice?

[PeterClemmensen · Accepted solution]

Hi and welcome to the SAS Community.

For a first time poster, you're doing very well 🙂 Nice explanation and data we can work with.

Try the code below and see if it works. Feel free to ask.

data have;
input Account $ AccountNumber $;
datalines;
Alpha ABC1
Beta  ABC2
Delta ABC3
;

data want;
   set have;
   do scale = 1 to 3;
      output;
   end;
run;

Result:

Account  AccountNumber  scale 
Alpha    ABC1           1 
Alpha    ABC1           2 
Alpha    ABC1           3 
Beta     ABC2           1 
Beta     ABC2           2 
Beta     ABC2           3 
Delta    ABC3           1 
Delta    ABC3           2 
Delta    ABC3           3 

[Pattyp33]

THANK YOU!! It worked perfectly!

Those dang do statements, something I should have payed more attention to in school lol

Thank you again!

[novinosrin]

Hi @Pattyp33  Sorry just noticed the thread already answered. One could think through other ways if we care-

data have;
input Account $ AccountNumber $;
datalines;
Alpha ABC1
Beta  ABC2
Delta ABC3
;
/*Create VIEW of your scale*/
data temp/view=temp;
 do scale=1 to 3;
  output;
 end;
run;
/*Cross join */
proc sql;
 create table want as
 select a.*,scale
 from have a,temp
 order by accountnumber, scale;
quit;