Help Needed for transpose

[Dana86]

Hi everybody,

I never learned SAS and I need an help to write my code.

I have one table structured in this way:

   DATE     HOUR    ROME    MILAN     NAPLES

    28/08/21    1            45            32            28

    28/08/21    2            37            22            25  

    28/08/21    3            33            26           27

from this table I need to create a txt files with this output:

    28/08/21    1            ROME      45            

    28/08/21    2            ROME      37  

    28/08/21    3            ROME      33

    28/08/21    1            MILAN     32

    28/08/21    2            MILAN     22

    28/08/21    3            MILAN     26

    28/08/21    1            NAPLES  28

    28/08/21    2            NAPLES  25

    28/08/21    3            NAPLES  27

can someone hel me to this piece of code?

Thanks in advance

[ballardw · Accepted solution]

---

@Dana86 wrote:

Hi everybody,

I never learned SAS and I need an help to write my code.

I have one table structured in this way:

   DATE     HOUR    ROME    MILAN     NAPLES

    28/08/21    1            45            32            28

    28/08/21    2            37            22            25  

    28/08/21    3            33            26           27

from this table I need to create a txt files with this output:

    28/08/21    1            ROME      45            

    28/08/21    2            ROME      37  

    28/08/21    3            ROME      33

    28/08/21    1            MILAN     32

    28/08/21    2            MILAN     22

    28/08/21    3            MILAN     26

    28/08/21    1            NAPLES  28

    28/08/21    2            NAPLES  25

    28/08/21    3            NAPLES  27

can someone hel me to this piece of code?

Thanks in advance

---

This will get something close. If the actual order of the cities is critical then additional information, such as all of the cities involved and the specific order would need to be provided.

The below creates a data set, the data step is how example data should be provided, the reshapes and orders the data.

data have;
  input DATE :ddmmyy10.    HOUR    ROME    MILAN     NAPLES;
  format date ddmmyy10.;
datalines;
    28/08/21    1            45            32            28
    28/08/21    2            37            22            25  
    28/08/21    3            33            26           27
;

proc transpose data=have out=want
     name=city ;
   by date hour;
run;

proc sort data=want;
   by date city hour;
run;

You do not specify any details of the type of text file you need. Proc Export will create simple text files. Or a data step will write to a text file if Export doesn't provide what you want.

[Dana86]

Thanks a lot! It works perfectly!!

[Astounding]

Taking your question literally that you really do have a SAS data set ("table") to begin and want to create a text file as output, here is a way to do that:

data _null_;
   file 'name of text file to hold results';
   do until (done1);
      set have end=done1;
      put date @12 hour @20 'ROME'  @29 rome;
   end;
   do until (done2);
      set have end=done2;
      put date @12 hour @20 'MILAN'  @29 milan;
   end;
   do until (done3);
      set have end=done3;
      put date @12 hour @20 'NAPLES'  @29 naples;
   end;
run;