PROC TRANSPOSE with multiple variables

[greg6363]

The var _ALL_ statement gets all the variables into a column but when I run the second PROC TRANSPOSE statement, I get the following note:  

NOTE: No variables to transpose.

Here is my current code:

data step1;

set have;

by ID1 ID2;

if first.ID2 then row=0;

row+1;

run;

proc transpose data=step1 out=step2;

by ID1 ID2 row;

var _ALL_;

run;

proc transpose data=step2 out=step3 (drop=_name_) delim=_;

by ID1 ID2;

id _name_ row;

run;

"HERE IS WHERE THE NOTE THAT STATES NO VARIABLES TO TRANSPOSE"

The output table has all the necessary fields but does not populate any data.  

Let me know what I am missing.  Thanks.

[Tom]

Same problem with PROC TRANSPOSE defaulting to only looking for numeric variables. Add VAR COL1; to that step.

[greg6363]

To the first PROC TRANSPOSE statement or the second PROC TRANSPOSE statement?

When I run the first PROC TRANSPOSE procedure, the output table (step2) has all the data and the field names needed to complete the second PROC TRANSPOSE statement.  Here is the layout:

ID1  ID2   row   NAME OF FORMER VARIABLE    LABEL OF FORMER VARIABLE   COL1

The values in the COL1 are arranged correctly and match with the field name values in the NAME/LABEL OF FORMER VARIABLE columns.

Let me know.  Thanks.

[greg6363 · Accepted solution]

I added the var COL1 code to the second PROC TRANSPOSE statement and it produced the output in the layout that I need in order to facilitate reporting.  I'm set.  I'll look at that macro to automate the process.  Thanks for the assistance. 

[ed_sas_member]

Hi @greg6363 

Do you expect something like this ?

proc sql noprint;
	select max(total) into: max_obs from (select count(*) as total from have group by ID1, ID2);
quit;

data want;
	set have;
	by ID1 ID2;
	
 	array ID3_(&max_obs) $;
 	array Date1_(&max_obs);
	array Amount_(&max_obs);
	array Date2_(&max_obs);
	array Code1_(&max_obs) $;
	array Date3_(&max_obs);
	array Action1_(&max_obs) $;
	array Indicator1_(&max_obs);
	array Indicator2_(&max_obs);
 	
 	if first.ID2 then do;
		counter=0;
		call missing(of ID3_(*));
		call missing(of Date1_(*));
		call missing(of Amount_(*));
		call missing(of Date2_(*));
		call missing(of Code1_(*));
		call missing(of Date3_(*));
		call missing(of Action1_(*));
		call missing(of Indicator1_(*));
		call missing(of Indicator2_(*));
	end;
	
 	counter+1;
 	
 	retain ID3_;
 	retain Date1_;
	retain Amount_;
	retain Date2_;
	retain Code1_;
	retain Date3_;
	retain Action1_;
	retain Indicator1_;
	retain Indicator2_;
 	
 	do i=1 to dim(ID3_);
		ID3_(counter)=ID3;
		Date1_(counter)=Date1;
		Amount_(counter)=Amount;
		Date2_(counter)=Date2;
		Code1_(counter)=Code1;
		Date3_(counter)=Date3;
		Action1_(counter)=Action1;
		Indicator1_(counter)=Indicator1;
		Indicator2_(counter)=Indicator2;
	end;
	
	format Date: MMDDYY8.;

	if last.ID2 then output;
	drop ID3 Date1 Amount Date2 Code1 Date3 Action1 Indicator1 Indicator2 counter i;

run;

[art297]

@greg6363 ,

As @Tom mentioned, the task can be made a lot simpler using the %transpose macro. e..g.:

data have ;
 input ID1 ID2 ID3 $ Date1 :mmddyy. Amount :comma. Date2 :mmddyy. Code1 $ Date3 :mmddyy. Action1 Indicator1 Indicator2 ;
cards;
6666 7777 LP219 7/20/19 $100 8/1/19 SDA 8/4/19 007 1 0
6666 7777 SN015 7/21/19 $200 8/4/19 BND 8/15/19 192 1 0
6666 7777 BP123 7/24/19 $250 8/12/19 COS 8/18/19 308 1 0
6666 7777 CD954 7/25/19 $150 8/15/19 DTE 8/22/19 482 1 0
6666 7777 FF773 7/28/19 $125 8/24/19 RUI 8/28/19 732 1 0
;

filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;

%transpose(data=have, out=want, by=ID1 ID2, autovars=all, delimiter=_)

Art, CEO, AnalystFinder.com