assign data from a column in an array

[vitalizer]

Hello,

I am very very new in SAS, knowing al little bit in VBA and so I have some difficulties with arrays.

My problem: I importet data from Excel. Two columns. The first colum  called "orginal" has numbers from 1 to 45. The second one called "replace" has also numers from 1 to 45 but in an other order as the column original.

My intended goal ist making an array original(1) with the first number in the column "original", original(2) with the 2nd one, and so on up to original(45). Even so an array replace(1) up to replace(45).

I managed it,  if I transpose the data in Excel in one row and name the first column original1, original 2 the 2nd and so on an make an array (45) original1-original45.

Is there a way in SAS to do so without transposing? I have many many columns in my datas and don't want 2x45 rows adding there. I need the variables for exchanging values.It works wit 45 orders in SAS, but I want to make the code more clear and easy reding with a loop, and therefor I need the variables.

I spend hours till I saw this Community. I hope someone can help me

This ist my code I did it without array variables:

this ist the excel-part:

(this is a one with only 11 datas, I think this shows how it is ment)

Thank you very much

[Reeza]

Post an example of what you have and what you want as an output. Arrays in SAS don't have the same context as in VBA or other languages and usually won't do what you want if you're thinking along those lines.

[Shmuel]

data have;

     input org rep $;

cards;

1 A

2 B

3 C

4 D

; run;

%let dim=4; /* adapt to your input = 45 */

data want;

   length rep1-rep&dim $3; /* adapt to max length of REP */

   set have end=eof;

   array or {*} org1-org&dim; 

   array rp {*} $ rep1-rep&dim;

    or(_N_) = org;

    rp(_N_) = rep;

    if eof then output;

run;

[vitalizer]

Hello Shmuel,

thanks a lot for your fast answer. I apologize, I Think I didn't explain excatly what I meant. My Problem: I import Data from Excel, wher in the column "Berechtigung" are up to 45 numbers, seperatet with a Space. The column has about 3000 entrys (rows). So for example one row  can have '1 15 18'. Each of this numbers I have to replace with other numbers an replace the space with a comma ','. 

I added in my question above the working programm which provides the exact result I want. My first question was how to get an array and your answer seems to be a good way (even when I run it, it has only values in rep4 org rep and org4???

But now I tried it first with given values to the array, But the results are not the same as  in my first working way. From originally 1817 rows there are in the result just 324 rows left, and the rows are multiple repeating.

So my question is: Is there a way in SAS to make the code in the way with do as quotet in the following programm or am I still to much in VBA-programming? I had to learn hard that arrays are realy different in SAS from other languages :-).

Thanks for answering.

Here my last try:

data work.Umwandlungsergebnis;
set work.BB_UEbersetzungstabelle;

length Berechtigung1 $120;
length org1-org18 $120;
length rep1-rep18 $120;

array or {*} $ org1-org18 ('18 ' '17 ' '16 ' '15 ' '14 ' '13 ' '12 ' '11 ' '10 ' '9 ' '8 ' '7 ' '6 ' '5 ' '4 ' '3 ' '2 ' '1 ');
array rp {*} $ rep1-rep18 (',20#' ',09#' ',09#' ',09#' ',25#' ',25#' ',23#' ',22#' ',30#' ',21#' ',15#' ',13#' ',14#' ',06#' ',10#' ',07#' ',05#' ',04#');

set work.DM2_BRANDENBURG;
Berechtigung1 = Berechtigung;

do i = 1 to 18;
Berechtigung1 = strip(tranwrd(Berechtigung1,or(i),rp(i)));
output;
end;

Berechtigung1 = compress(Berechtigung1,'#');

keep Berechtigung Berechtigung1;

run;

[Shmuel]

Can original column contain non numeric value? IF negative better read this column as numeric and you can use format to do the create the new column. I understand you have two tables - (1) defines the transformation/replacement (2) data to deal with:

proc format lib=work;
  value replace
	    1 = '4'
        2 = '5'
        3 = '7'
		4 = '6'
		5 = '14'
		6 = '13'
		7 = '10'
		8 = '15'
     9-11 = '9'
; run;

data want;
 set work.DM2_BRANDENBURG;
     new_var = put(org_var,replace.);
run;	 

Pay attention, if original column is char type then you may have issues as '1 ' ne ' 1' for excmaple.

[Reeza]

1. Join instead of do data lookups
2. Use a format instead of lookups
3. Use a hash table instead of lookups
4. Use a temporary array loaded from a single file
5. Use PROC TRANSPOSE