topic Re: sort data in SAS Studio

sort data

jcjerim — Wed, 04 Oct 2017 16:42:39 GMT

Good morning my SAS friends:

Here is a question about SAS basic computing

I have a very large database I want to sort it by day, time, and variables, as an example I did a simulation, I would greatly appreciate your help my friends.

Considering these data:

                    V1 (temperature)                       V2 (humedity) .............................                       VX(…………)

   Hour_0 Hour_1 Hour_2 Houro_3   Hour_4   Hour_0   Hour_1 Hour_2 Hour_3 Hour_4 Hour_0 Hour_1 Hour_2 Hour_3 Hour_4
Day 1    22        24   26      28   31      75   78          78            87       91          A C      B         F          D
Day 2    28        29   31      28   32      88   90      85            73       84          C E      A         S          S
Day 3    22        26     27      23               24      79         75          76            15       29          M      L      P         Y        K


   For the purpose of this table

day   horario    V1(Temperature)    V2 (Humidity)……   Vx (……..)

da1   Horario_0    22          75   A
da1   Horario_1      24          78      C
da1   Horario_2 26          78       B
da1   Horario_3            28          87         F
da1   Horario_4      31                      91   D
da2   Horario_0 28      88       C
da2   Horario_1 29      90         E
da2   Horario_2 31                      85         A
da2   Horario_3 28                      73         S
da2   Horario_4            32          84         S
da3   Horario_0      22      79         M
da3   Horario_1 26      75                       L
da3   Horario_2            27      76         P
da3   Horario_3 23      15         Y
da3   Horario_4      24                      29      K

Re: sort data

Reeza — Wed, 04 Oct 2017 16:54:14 GMT

Are you trying to sort data or reshape your data, ie turn your columns into rows?

If its a SORT then use PROC SORT.

If it's a transformation, then use PROC TRANSPOSE or an Array.

If you search TRANSPOSE UCLA SAS you'll find some good tutorials that walk your through how to transpose your data.

@jcjerim wrote:

Good morning my SAS friends:

Here is a question about SAS basic computing

I have a very large database I want to sort it by day, time, and variables, as an example I did a simulation, I would greatly appreciate your help my friends.

Considering these data:

                    V1 (temperature)                       V2 (humedity) .............................                       VX(…………)

   Hour_0 Hour_1 Hour_2 Houro_3   Hour_4   Hour_0   Hour_1 Hour_2 Hour_3 Hour_4 Hour_0 Hour_1 Hour_2 Hour_3 Hour_4
Day 1    22        24   26      28   31      75   78          78            87       91          A C      B         F          D
Day 2    28        29   31      28   32      88   90      85            73       84          C E      A         S          S
Day 3    22        26     27      23               24      79         75          76            15       29          M      L      P         Y        K


   For the purpose of this table

day   horario    V1(Temperature)    V2 (Humidity)……   Vx (……..)

da1   Horario_0    22          75   A
da1   Horario_1      24          78      C
da1   Horario_2 26          78       B
da1   Horario_3            28          87         F
da1   Horario_4      31                      91   D
da2   Horario_0 28      88       C
da2   Horario_1 29      90         E
da2   Horario_2 31                      85         A
da2   Horario_3 28                      73         S
da2   Horario_4            32          84         S
da3   Horario_0      22      79         M
da3   Horario_1 26      75                       L
da3   Horario_2            27      76         P
da3   Horario_3 23      15         Y
da3   Horario_4      24                      29      K

Re: sort data

PGStats — Wed, 04 Oct 2017 21:58:47 GMT

Since this is not a straitforward transposition with proc transpose, it is probably simpler to use a bit of programming with arrays:

data have;
input day &$ temp0-temp4 humid0-humid4 (letter0-letter4) ($);
datalines;
Day 1  22 24 26 28 31 75 78 78 87 91 A C B F D
Day 2  28 29 31 28 32 88 90 85 73 84 C E A S S
Day 3  22 26 27 23 24 79 75 76 15 29 M L P Y K
;

data want;
set have;
array t temp: ;
array h humid: ;
array l letter: ;

do i = 1 to dim(t);
    time = i-1;
    temp = t{i};
    humid = h{i};
    letter = l{i};
    output;
    end;
keep day time temp humid letter;
run;

proc print data=want noobs; run;

Re: sort data

mkeintz — Sat, 07 Oct 2017 02:43:10 GMT

If you're reading from raw data, you can put an INPUT statement inside a do hour=0 to 4 loop:

data want (drop=dum: j);
  infile datalines truncover ;
  input date :$5. @7 @;
  do hour=0 to 4;
    input  t (dum1-dum4) (:$8.)
           h (dum1-dum4) (:$8.)
           v :$8. 
     @7 @;
    do j=1 to hour+1;
      input dum1 :$8. @;
    end;
    output;
  end;
datalines;
Day1    22        24     26      28               31          75           78            78            87       91          A          C          B         F            D
Day2    28        29     31      28               32          88           90            85            73       84          C          E          A         S            S
Day3    22        26     27      23               24          79           75            76            15       29          M          L          P         Y            K
run;

Notes:

The trailing @in te input statements mean do not relinquish the input dataline.
The @7 means to point at byte 7 of the input data (i.e.right after the date