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:
I am using SAS 9.4 on Windows 10
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;
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/
Does your real file have anything to uniquely identify the rows?
No, but it would be easy enough to add an ID either in a DATA STEP or in the .csv file where this started.
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).
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;
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.