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.
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.
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.
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;
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
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.