BookmarkSubscribeRSS Feed
556
Calcite | Level 5 556
Calcite | Level 5

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

16 REPLIES 16
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

@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

556
Calcite | Level 5 556
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

556
Calcite | Level 5 556
Calcite | Level 5

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



Reeza
Super User

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.

556
Calcite | Level 5 556
Calcite | Level 5

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)

Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

KachiM
Rhodochrosite | Level 12

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?

556
Calcite | Level 5 556
Calcite | Level 5

I have attached the datafile

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

Reeza
Super User

Seems like straightforward proc transpose to me.

proc transpose data=have out=want;

by mon;

id stations;

var mthavg;

run;

556
Calcite | Level 5 556
Calcite | Level 5

But how to store the Stations name to 1-21

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 16 replies
  • 1786 views
  • 0 likes
  • 4 in conversation