topic Re: Build a lookup table by using multidimensional arrays in SAS Procedures

Build a lookup table by using multidimensional arrays

556 — Thu, 16 Apr 2015 13:04:45 GMT

table1

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

Re: Build a lookup table by using multidimensional arrays

RW9 — Thu, 16 Apr 2015 13:36:19 GMT

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;

Re: Build a lookup table by using multidimensional arrays

Reeza — Thu, 16 Apr 2015 13:53:48 GMT

@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

Re: Build a lookup table by using multidimensional arrays

556 — Thu, 16 Apr 2015 14:07:37 GMT

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?

table 1

table 2

Re: Build a lookup table by using multidimensional arrays

RW9 — Thu, 16 Apr 2015 14:16:12 GMT

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;

Re: Build a lookup table by using multidimensional arrays

556 — Thu, 16 Apr 2015 14:25:17 GMT

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

Re: Build a lookup table by using multidimensional arrays

Reeza — Thu, 16 Apr 2015 14:33:53 GMT

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.

Re: Build a lookup table by using multidimensional arrays

556 — Thu, 16 Apr 2015 14:41:30 GMT

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

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)

Re: Build a lookup table by using multidimensional arrays

RW9 — Thu, 16 Apr 2015 14:44:23 GMT

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;

Re: Build a lookup table by using multidimensional arrays

Reeza — Thu, 16 Apr 2015 14:44:24 GMT

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

Re: Build a lookup table by using multidimensional arrays

KachiM — Thu, 16 Apr 2015 14:45:57 GMT

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?

Re: Build a lookup table by using multidimensional arrays

556 — Thu, 16 Apr 2015 14:51:35 GMT

I have attached the datafile

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

Re: Build a lookup table by using multidimensional arrays

Reeza — Thu, 16 Apr 2015 15:01:36 GMT

Seems like straightforward proc transpose to me.

proc transpose data=have out=want;

by mon;

id stations;

var mthavg;

run;

Re: Build a lookup table by using multidimensional arrays

KachiM — Thu, 16 Apr 2015 15:03:06 GMT

It is difficult to get your SAS data set. Better for me is the ASCII file.

Re: Build a lookup table by using multidimensional arrays

556 — Thu, 16 Apr 2015 15:15:21 GMT

But how to store the Stations name to 1-21

Re: Build a lookup table by using multidimensional arrays

Reeza — Thu, 16 Apr 2015 15:24:44 GMT

TRY IT!

Re: Build a lookup table by using multidimensional arrays

Reeza — Fri, 17 Apr 2015 14:30:01 GMT

You should also add the IDLabel so the station names look 'nice' in reports:

proc transpose data=have out=want;

by mon;

id stations;

idlabel stations;

var mthavg;

run;