02-15-2017 05:49 PM
I am trying to dynamically create tables, using a macro, from a larger table based on the column names (so I don't have to do a 40 select union) . the final goal is a crosstab table. The column names in the source table are like
AB1_AccountID AB1_Name AB1_Address AB2_AccountID AB2_Name AB2_Address etc. ,
1234 Besty Ross 123 MainSt 34567 Frank N Stein PO BOX 66
I'd like a table of the AB1 data, the AB2 data etc. that looks like
AB1_AccountID AB1_Name AB1_Address
12345 Besty Ross 123 Main St
34567 Frank N Stein PO Box 66
I was trying to use a macro to create the individual datasets :
%do j=1 %to 40;
create table yaddayadda_ab1 as
select "&brnum"||_AccountID from yaddayadda_wrk; run;
I've tried all sorts of variations on having the variables in " and not, and the closest I can get is an error saying the column names BR1, _AccountID are not found in the dataset. What am I doing wrong? Is this really the dorkiest way to do this or is there a better way? I am new to SAS programming, am I making this harder than it has to be?
I *might* could do without the individual datasets if it's easier.
02-15-2017 06:59 PM
Your answer could be answered more easily if we knew what your ultimate crosstabs have to look like.
However, that said, you can probably get by a lot easier if you make the file a long rather than a wide file. With a long file you can probably run just one proc freq, using AB as a by variable.
If all of your variables are character variables, you could do something like the following to make the file long:
data need (keep=AB AccountID Account_Name Account_Address); set have; array stuff(*) AB1_AccountID--AB40_Address; do i=0 to 39; if not missing(stuff(i*3+1)) then do; AB=i+1; AccountID=stuff(i*3+1); Account_Name=stuff(i*3+2); Account_Address=stuff(i*3+3); output; end; end; run;
Art, CEO, AnalystFinder.com
02-22-2017 11:48 AM
02-15-2017 08:45 PM
This is a good candidate for using PROC TRANSPOSE. Let's make some dummy data.
data have ; length AB1_AccountID $10 AB1_Name $30 AB1_Address $100 AB2_AccountID $10 AB2_Name $30 AB2_Address $100 AB3_AccountID $10 AB3_Name $30 AB3_Address $100 ; infile cards dsd dlm='|' flowover ; input AB1_AccountID -- AB3_Address ; cards; 1234|Besty Ross|123 Main St 34567|Frank N Stein|PO BOX 66 567|Jane Doe|3 North Broadway ;
So transpose it once to convert the values into one long column.
proc transpose data=have out=tall ; var AB1_AccountID -- AB3_Address ; run;
The edit the result and pull the metadata out of the variable names. This code is assuming the names all start ABnn_ where nn could be 1 or more digits. So can convert the NN into the new field RECNO and remove it from the _NAME_ field generated by PROC TRANSPOSE.
data tall; set tall ; recno = input(substr(scan(_name_,1,'_'),3),10.); _name_ = substr(_name_,length(scan(_name_,1,'_'))+2); run;
Now resort the data and call PROC TRANSPOSE again.
proc sort ; by recno _name_; run; proc transpose data=tall out=want (drop=_:); by recno ; id _name_; var col1 ; run;
02-17-2017 04:21 AM