I'm trying to transpose a data set using a single data step (NOT using Proc Transpose):
Original Data Set:
DATA WORK.SwimMinutes;
INFILE DATALINES;
INPUT Name $ 1-5
Age 7-8
Mon 10-11
Tue 13-14
Wed 16-17;
DATALINES;
Ann 26 51 19 22
Bob 30 43 20 60
Chris 41 48 . 36
Dina 21 32 57 22
;
And I'm trying to tranpose it so it looks like this:
| Name | Age | Day | Minutes |
1 | Ann | 26 | 1 | 51 |
2 | Ann | 26 | 2 | 19 |
3 | Ann | 26 | 3 | 22 |
4 | Bob | 30 | 1 | 43 |
5 | Bob | 30 | 2 | 20 |
6 | Bob | 30 | 3 | 60 |
… | … | … | … | … |
12 | Dina | 21 | 3 | 22 |
… | … | … | … | … |
I was told I could use an array to do this but since I struggle with arrays this was my attempt:
DATA WORK.SwimLong3;
KEEP Name Age Day Minutes;
SET WORK.SwimMinutes;
Day= "Mon";
Minutes= Mon;
IF Day= "Mon" THEN Day=1;
OUTPUT;
Day= "Tue";
Minutes= Tue;
IF Day= "Tue" THEN Day=2;
OUTPUT;
Day= "Wed";
Minutes= Wed;
IF Day= "Wed" THEN Day=3;
OUTPUT;
RUN;
This looks correct but the Day variable is still a character variable, not numeric and I'm not sure how to change that within the code.
If you could help either with modifying my current attempt, or give me some help with creating an array, that would be wonderful.
Thank you!
Amanda
Hi Amanda,
I did something similar to the above post but used a second array --
DATA WORK.data1;
SET WORK.data;
KEEP Name Age Day Minutes;
ARRAY Weekday {3} _TEMPORARY_ (1,2,3);
ARRAY Mins {3} Mon -- Wed;
DO i = 1 TO DIM(Mins);
Day = Weekday{i};
Minutes = Mins{i};
OUTPUT WORK.data2;
END;
RUN;
Hope this helps!
Kaitlin
Hi Amanda,
Thank you for sending usable data.
Below is a code to tranpose without a proc.
DATA WORK.SwimMinutes;
INFILE DATALINES;
INPUT Name $ 1-5
Age 7-8
Mon 10-11
Tue 13-14
Wed 16-17;
DATALINES;
Ann 26 51 19 22
Bob 30 43 20 60
Chris 41 48 . 36
Dina 21 32 57 22
;
run;
data have;
set SwimMinutes;
by name;
*define array;
array cols[3] mon tue wed;
do i=1 to dim(cols);
*input array counter to day variable. This is week counter;
day=i;
*assign mon tue wed variable values to minute variable;
minutes=cols[i];
*output by observation;
output;
end;
*drop mon tue wed by name range list;
drop mon-numeric-wed i;
run;
Hi Amanda,
I did something similar to the above post but used a second array --
DATA WORK.data1;
SET WORK.data;
KEEP Name Age Day Minutes;
ARRAY Weekday {3} _TEMPORARY_ (1,2,3);
ARRAY Mins {3} Mon -- Wed;
DO i = 1 TO DIM(Mins);
Day = Weekday{i};
Minutes = Mins{i};
OUTPUT WORK.data2;
END;
RUN;
Hope this helps!
Kaitlin
Also, to better explain how the arrays are being used in the do loop --
If i=1, Minutes = Mins{Mon} = 51, 43, 48, 32 (one result associated with each person) and Day = Weekday{1} = 1 (repeated for each person), as shown below. The do loop repeats for 2 and 3 and therefore we are left with 12 observations, as desired.
I'm not sure I would bother with arrays, when use of data set name parameters will do the work:
DATA WORK.SwimMinutes;
INFILE DATALINES;
INPUT Name $ 1-5
Age 7-8
Mon 10-11
Tue 13-14
Wed 16-17;
DATALINES;
Ann 26 51 19 22
Bob 30 43 20 60
Chris 41 48 . 36
Dina 21 32 57 22
;
data want;
set swimminutes (rename=(mon=minutes) drop=tue wed)
swimminutes (rename=(tue=minutes) drop=mon wed)
swimminutes (rename=(wed=minutes) drop=mon tue);
by name;
run;
You are overcomplicating things by a mile.
This is non-array code that will do it:
data SwimLong3;
set SwimMinutes;
Minutes = Mon;
Day =1;
output;
Minutes = Tue;
Day = 2;
output;
Minutes = Wed;
Day = 3;
output;
keep Name Age Day Minutes;
run;
From that, it is easy to see that you can overlay a three-member array over Mon--Wed and use Day as index:
data SwimLong3;
set SwimMinutes;
array days {3} Mon--Wed;
do Day = 1 to 3;
minutes = days{Day};
output;
end;
keep name age day minutes;
run;
Edit: fixed a typo (Days --> Day) in the DO statement
data want; set SwimMinutes; array VarArray(*) Mon Tue Wed; do i=1 to dim(VarArray); Day=i; Minutes=Vararray(i); output; end; drop i Mon Tue Wed; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.