Hi,
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
Table AB1_Recs
AB1_AccountID AB1_Name AB1_Address
12345 Besty Ross 123 Main St
34567 Frank N Stein PO Box 66
.etc...
I was trying to use a macro to create the individual datasets :
%macro Make40Tabless;
%do j=1 %to 40;
%LET brnum=BR&j;
PROC SQL;
create table yaddayadda_ab1 as
select "&brnum"||_AccountID from yaddayadda_wrk; run;
...
%end;
%mend;
%Make40Tables
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.
Thanks
Cat
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;
HTH,
Art, CEO, AnalystFinder.com
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;
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.