topic Transpose using Data Step in New SAS User

Transpose using Data Step

abrice520 — Wed, 18 Nov 2020 03:31:13 GMT

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

Re: Transpose using Data Step

hhinohar — Wed, 18 Nov 2020 04:28:31 GMT

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;

Re: Transpose using Data Step

kaitlineolson7 — Thu, 10 Dec 2020 15:12:32 GMT

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

Re: Transpose using Data Step

kaitlineolson7 — Wed, 18 Nov 2020 04:52:10 GMT

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.

Re: Transpose using Data Step

mkeintz — Wed, 18 Nov 2020 05:39:27 GMT

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;

Re: Transpose using Data Step

Kurt_Bremser — Thu, 19 Nov 2020 05:09:37 GMT

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

Re: Transpose using Data Step

abrice520 — Thu, 19 Nov 2020 00:07:11 GMT

Thanks Kaitlin!

Re: Transpose using Data Step

Xianyu — Thu, 10 Aug 2023 07:26:10 GMT

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;