Help using Base SAS procedures

Build a lookup table by using multidimensional arrays

Reply
Occasional Contributor 556
Occasional Contributor
Posts: 13

Build a lookup table by using multidimensional arrays

1.png

table1

2.png

table2

How to use multidimensional arrays to change the table structure from table 1 to table2 ??

I only know how to change the structure from table 2 to table1...............

I have checked with many online resources, it should be using multidimensional arrays to do it

Super User
Super User
Posts: 7,565

Re: Build a lookup table by using multidimensional arrays

Well, it doesn't have to be multi-dimensional arrays, as you basically have two sets, you can proc transpose each and then merge back.  Its quite simple for two sets.  If you really want to do it with arrays though:

data want (drop=timept htm wtkg);

     set have;

     array htm{5} 8.;  /* Yes you could set it up as a 2 * 5 array, I think it just reads easier */

     array wtkg{5} 8.;

     retain htm1-htm5 wtkg1-wtkg5;

     htm{timept}=htm;

     wtkg{timept}=wtkg;

     if timept=5 then output;

run;

Super User
Posts: 18,601

Re: Build a lookup table by using multidimensional arrays

@rw9 solution with two arrays is better in the long run - because what happens if you have a character variable to transpose? An array can only have one type so having multiple arrays makes it easier.

To me this is more of a transpose problem than a lookup problem.

Adding on to RW9's solution, you'll need to reinitialize the array at each ID if you don't have 5 of each observation. If you do, his solution is fine as is.

data want (drop=timept htm wtkg);

     set have;

    by id;

     array htm{5} 8.;  /* Yes you could set it up as a 2 * 5 array, I think it just reads easier */

     array wtkg{5} 8.;

     retain htm1-htm5 wtkg1-wtkg5;

         if first.id then do i=1 to dim(htm);

        htm(i)=.; wtgk(i)=.;

   end;

     htm{timept}=htm;

     wtkg{timept}=wtkg;

     if timept=5 then output;

run;

SAS Learning Module: Reshaping wide to long using a data step

Occasional Contributor 556
Occasional Contributor
Posts: 13

Re: Build a lookup table by using multidimensional arrays

I try to apply the code you provided to another similar dataset but I cannot get the expected result...

table two should have 3 rows and 21 columns

and mentioned a important problem, in this case I want to store the name of 21 stations into 1-21 by using array and also Mon 1, 8, 11 into 1-3. Multidimensional arrays should be used? right?

1.png

table 1

2.png

table 2

Super User
Super User
Posts: 7,565

Re: Build a lookup table by using multidimensional arrays

No, you just need to modify the code to fit your data.  We can only work with what you provide.  So first step is deciding what the station numbers should be, and setting that accordingly;

data inter;

     set have;

     select(station);

          when("CHEP LAK KOK") ord=1;

          when("CHEUNG CHAO" ord=2;

           ...;

     end;

run;

Now use this data to do the loop:

data want (drop=);

     set inter;

     array station{21} 8.;

     by mon;

     station{ord}=mthavg;

     if last.mon then output;

run;

Occasional Contributor 556
Occasional Contributor
Posts: 13

Re: Build a lookup table by using multidimensional arrays

But every time the name of the stations change, i need to to modify the code...

using array to store the variable name into 1-21 would be more convenient...

But again, i spent half day to think about the code and yet, i still cannot solve it...



Super User
Posts: 18,601

Re: Build a lookup table by using multidimensional arrays

556 wrote:

But every time the name of the stations change, i need to to modify the code...


Start over. Explain all of your problem, given what you've stated @RW9 has provided the best solution. But if you keep changing the problem, the best solution will change as well.

Occasional Contributor 556
Occasional Contributor
Posts: 13

Re: Build a lookup table by using multidimensional arrays

1.png

Table 1 (Total rows: 63 Total columns: 3)

2.png

Table2 (Total row: 3 Total columns: 21)

Store the name of the stations (total 21 stations) to 1 - 21 by using array

Change structure from table 1 to table 2

assuming we did not know about the exact content of variable  STATIONS but total number of are STATIONS: 21)

Super User
Posts: 18,601

Re: Build a lookup table by using multidimensional arrays

I don't see how MON is coming in to your second data set.

Super User
Super User
Posts: 7,565

Re: Build a lookup table by using multidimensional arrays

Then instead of:

data inter;

     set have;

     select(station);

          when("CHEP LAK KOK") ord=1;

          when("CHEUNG CHAO" ord=2;

           ...;

     end;

run;

Do something like:

proc sql;

     create table TEMP as

     select     STATION,

                   MONOTONIC as ORD

     from        (select distinct STATION from HAVE);

     select     MAX(ORD)

     into        :ARRAY_TOT         

     from       TEMP;

     create table WITH_ORD as

     select     A.*,

                   B.ORD

     from       HAVE A

     left join   TEMP B

     on          A.STATION=B.STATION;

quit;    

And your datastep:

Now use this data to do the loop:

data want (drop=);

     set inter;

     array station{&ARRAY_TOT.} 8.;

     by mon;

     station{ord}=mthavg;

     if last.mon then output;

run;

Super Contributor
Posts: 276

Re: Build a lookup table by using multidimensional arrays

Just copy/paste of your data is not possible. I have a solution to your problem. Can you place the data in an ASCII format?

Occasional Contributor 556
Occasional Contributor
Posts: 13

Re: Build a lookup table by using multidimensional arrays

I have attached the datafile

It should be 1 8 11, just typo:smileyblush:

Attachment
Super User
Posts: 18,601

Re: Build a lookup table by using multidimensional arrays

Seems like straightforward proc transpose to me.

proc transpose data=have out=want;

by mon;

id stations;

var mthavg;

run;

Occasional Contributor 556
Occasional Contributor
Posts: 13

Re: Build a lookup table by using multidimensional arrays

But how to store the Stations name to 1-21

Super User
Posts: 18,601

Re: Build a lookup table by using multidimensional arrays

TRY IT!

Ask a Question
Discussion stats
  • 16 replies
  • 540 views
  • 0 likes
  • 4 in conversation