Transpose using Data Step

[abrice520]

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

[hhinohar]

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;	

[kaitlineolson7 · Accepted solution]

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

[kaitlineolson7]

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.

[abrice520]

Thanks Kaitlin!

[mkeintz]

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;

[Kurt_Bremser]

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 

[Xianyu]

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;