DATA Step, Macro, Functions and more

Transposing dataset using do loops and arrays.

Accepted Solution Solved
Reply
Contributor sss
Contributor
Posts: 65
Accepted Solution

Transposing dataset using do loops and arrays.

Hi All,

 

 

Used following code to create dataset  Matrix dataset.

data Matrix(drop= i j);                                                                                                                

array r(5);                                                                                                                           

  do i=0 to 20 by 5;                                                                                                                   

     do j=1 to 5;                                                                                                                     

       r(j)=i+j;                                                                                                                        

     end;                                                                                                                              

    output;                                                                                                                             

  end;

 

Below transpose code transpose the dataset as i required. but i would like to do same with dataset using do loops and arrays.

 

proc Transpose prefix=r out=Transpose(drop=_NAME_);

 

Below code could not give me same output as proc transpose. could you please help me to transpose dataset in datastep?

data Transpose2(drop= i j r1-r5 flag);

set Matrix;           

flag=0;

array v(5);                                                                                                                        

array r(5);                         

 

  do i=1 to 5;

            do j=1 to 5;

                  _N_=j;

                  v(j)=r(i);

            end;

  end;

run;

I am trying to read first column of data set Matrix and storing it as a first row in data set Transpose. I am using _N_ automatic variable to switch between rows of dataset matrix so that I can read all values of first column.

Here I read 1st value of 1st column(then 2nd value of 1st column & so on), and then switching to second row by setting the value of _N_to 2 (so that SAS will jump to 2nd row in PDV.) and reading the 3rd value of 1st column & so on….. But it’s not working. Is this right way? Please give any hint about the logic or hint related to using array in different way.


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Valued Guide
Posts: 765

Re: Transposing dataset using do loops and arrays.

[ Edited ]

Hi ...

 

data matrix;
array r(5);
do i=0 to 20 by 5;
do j=1 to 5;
  r(j)=i+j;
end;
output;
end;
keep r: ;
run;


data new;
array r(5);
array v(5);
do i=1 to 5;
do j=1 to 5;
  set matrix point=j;
  v(j) = r(i);
end;
  output;
end;
stop;
keep v: ;
run;

 

data set NEW ...

Obs v1 v2 v3 v4 v5

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

 

If you wanted to make the transpose data step general, you could try ...

 

proc sql noprint;
select nvar, nlobs into :nvars trimmed, :nobs trimmed from dictionary.tables
where libname = 'WORK' and memname = 'MATRIX';
quit;

 

data new;
array r(&nvars);
array v(&nobs);
do i=1 to &nvars;
do j=1 to &nobs;
  set matrix point=j;
  v(j) = r(i);
end;
  output;
end;
stop;
keep v: ;
run;

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,419

Re: Transposing dataset using do loops and arrays.

Sorry, your post is quite unclear to me.  Please provide test data, in the form of a dataset, and what the output should look like.

Super User
Posts: 17,912

Re: Transposing dataset using do loops and arrays.

I don't think it makes sense to do this, except possibly as an exercise. If you want to work with Matrices its best to use SAS IML. Otherwise Proc Transpose is efficient.
New Contributor
Posts: 2

Re: Transposing dataset using do loops and arrays.

Hey.

 

See that.

 

DATA FINAL_1;
	SET ORIGEM;

	BY KEY;

	IF FIRST.KEY THEN
		MAX_KEY = 1;
	ELSE
		MAX_KEY + 1;
RUN;

PROC SQL NOPRINT;
SELECT COMPRESS(PUT(MAX(MAX_KEY),2.)) INTO:MAX_REF FROM FINAL_1;
QUIT;

%PUT ***&MAX_REF***;

DATA 	WORK.FINAL_2;
			SET FINAL_1;
 	BY MAX_KEY;

 	ARRAY TRANSPOSE     	{&MAX_REF.}$30.			TRANSPOSE_01 - TRANSPOSE_&MAX_REF.;
	
	IF FIRST.KEY THEN 
	DO;
		J = 1;
		DO WHILE (J <= &MAX_REF.);
			VARIABLE_TO_TRANS[J]	= "";
			J = J + 1;
		END;
		DROP J;
	END;

	VARIABLE_TO_TRANS[MAX_KEY] = TRANSPOSE;
		
	RETAIN	TRANSPOSE;
	
	IF LAST.KEY THEN 
		OUTPUT WORK.FINAL_2;
RUN;

 

 

Thanks.

Valued Guide
Posts: 765

Re: Transposing dataset using do loops and arrays.

Hi.  FYI and just a suggestion.

 

You don't need the COMPRESS and PUT functions when creating the macro variable in PROC SQL.  There is no data type for a macro variable value (it's just text) so the PUT isn't needed.  The TRIMMED option will get rid of all the leading blanks.

 

For example ...

 

proc sql noprint;
select compress(put(max(age),2.)), max(age), max(age) into :max1, :max2, :max3 trimmed from sashelp.class;
quit;

%put |&max1|;
%put |&max2|;
%put |&max3|;

 

the LOG ...

 

376 %put |&max1|;
|16|
377 %put |&max2|;
|      16|
378 %put |&max3|;
|16|

Solution
‎09-25-2015 06:23 AM
Valued Guide
Posts: 765

Re: Transposing dataset using do loops and arrays.

[ Edited ]

Hi ...

 

data matrix;
array r(5);
do i=0 to 20 by 5;
do j=1 to 5;
  r(j)=i+j;
end;
output;
end;
keep r: ;
run;


data new;
array r(5);
array v(5);
do i=1 to 5;
do j=1 to 5;
  set matrix point=j;
  v(j) = r(i);
end;
  output;
end;
stop;
keep v: ;
run;

 

data set NEW ...

Obs v1 v2 v3 v4 v5

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

 

If you wanted to make the transpose data step general, you could try ...

 

proc sql noprint;
select nvar, nlobs into :nvars trimmed, :nobs trimmed from dictionary.tables
where libname = 'WORK' and memname = 'MATRIX';
quit;

 

data new;
array r(&nvars);
array v(&nobs);
do i=1 to &nvars;
do j=1 to &nobs;
  set matrix point=j;
  v(j) = r(i);
end;
  output;
end;
stop;
keep v: ;
run;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 444 views
  • 0 likes
  • 5 in conversation