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
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.