Question about trannsposing

[plf515]

I am not sure why I find TRANSPOSE hard to learn but I do.  In any case, now I have this data:

data have;
  input cat1_s1_Andrew cat1_s2_Andrew cat2_s1_Andrew cat2_s2_Andrew cat3_s1_Andrew cat3_s2_Andrew cat4_s1_Andrew cat4_s2_Andrew cat4_s2_Mikhail cat1_s1_Mikhail cat1_s2_Mikhail cat2_s1_Mikhail cat2_s2_Mikhail cat3_s1_Mikhail cat3_s2_Mikhail cat4_s1_Mikhail horiz vert;
  datalines;
   0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 
   0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 
   0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 1
;
run;

but with about 1700 rows and not all 0's.

What I would like is a data set with these variables:

• cat (from 1 to 4, taken from the suffix to cat
• s (1 or 2, from the suffix to s)
• Name (Andrew or Mikhail)
• Horiz
• Vert
• The value (mostly 0's, above, but not in my full data set)

I am using SAS 9.4 on Windows 10

[VDD]

here is a link to proc transpose that have good examples with step by step instructions.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

[Tom]

Does your real file have anything to uniquely identify the rows?

[plf515]

No, but it would be easy enough to add an ID either in a DATA STEP or in the .csv file where this started.

[Tom]

Do you only have the SAS dataset?

Or do you have a raw text file like your example data?

It is trivial to read the raw data directly if you have it.  (Well not so trivial with HORIZ and VERT at the END of the line instead of the BEGINNING).

[plf515]

It started as a .csv file, but I had to create horiz and vert from another variable.

My code for reading it and creating what I have is

libname jeff "C:\personal\Consults\Jeff Sterk";

PROC IMPORT OUT= jeff.sixteen 
            DATAFILE= "C:\personal\Consults\Jeff Sterk\Unsorted16.csv" 
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2; 
RUN;


data jeff.sixteen;
 set jeff.sixteen;
 horiz = mod(location-1, 27);
 vert = int(location/27) + 1;
 drop location;
run;

[Tom]

Don't use PROC IMPORT to read simple text files.  It just has to make (uneducated) guesses about what is in the data.

So if you know the order of the columns then just read the values in that order.

data want;
  *infile "C:\personal\Consults\Jeff Sterk\Unsorted16.csv"  dsd truncover firstobs=2;
  length Location vert horiz 8 cat 8 s 8 Name $20 ;
  input location @;
  horiz = mod(location-1, 27);
  vert = int(location/27) + 1;
  do name='Andrew','Mikhail';
    do cat=1 to 4;
      do s=1 to 2 ;
        input value @;
        output;
      end;
    end;
  end;
  datalines;
 1  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
 2  21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 
 3  41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 
;
run;

Obs    Location    vert    horiz    cat    s     Name      value

  1        1         1       0       1     1    Andrew        1
  2        1         1       0       1     2    Andrew        2
  3        1         1       0       2     1    Andrew        3
  4        1         1       0       2     2    Andrew        4
  5        1         1       0       3     1    Andrew        5
  6        1         1       0       3     2    Andrew        6
  7        1         1       0       4     1    Andrew        7
  8        1         1       0       4     2    Andrew        8
  9        1         1       0       1     1    Mikhail       9
 10        1         1       0       1     2    Mikhail      10
 11        1         1       0       2     1    Mikhail      11
 12        1         1       0       2     2    Mikhail      12
 13        1         1       0       3     1    Mikhail      13
 14        1         1       0       3     2    Mikhail      14
 15        1         1       0       4     1    Mikhail      15
 16        1         1       0       4     2    Mikhail      16
 17        2         1       1       1     1    Andrew       21
 18        2         1       1       1     2    Andrew       22
 19        2         1       1       2     1    Andrew       23
 20        2         1       1       2     2    Andrew       24

[FreelanceReinh · Accepted solution]

You could use an array instead of PROC TRANSPOSE:

data want(keep=cat s name horiz vert value);
length cat s 8 name $7;
set have;
array v cat1_s1_Andrew--cat4_s2_Andrew cat1_s1_Mikhail--cat4_s1_Mikhail cat4_s2_Mikhail;
do over v;
  cat=input(char(vname(v),4),1.);
  s=input(char(vname(v),7),1.);
  name=substr(vname(v),9);
  value=v;
  output;
end;
run;