How do I create 'dynamic' sql in amarco? SAS 9

Reply
Occasional Contributor
Posts: 6

How do I create 'dynamic' sql in amarco? SAS 9

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

Esteemed Advisor
Posts: 7,301

Re: How do I create 'dynamic' sql in amarco? SAS 9

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

Occasional Contributor
Posts: 6

Re: How do I create 'dynamic' sql in amarco? SAS 9

Hello, First many apologies for the response delay, my hair was afire with some other issues. Secondly, thank you for the responses. I am going to try both the solutions but wanted to give a bit more info. The incoming data is a flat file that has a main person (with similar info, plus transaction info) and then each AB associated with the transaction so:
Main Person Account ID, Main Person Name, Main Person Address, ...AB1 AccountID, AB1_Name...-AB40 AccountID...I need to transpose the file to process each AB group and then un-transpose and rebuild the file as input so i can then recreate file on output .I will give these solutions a whirl and let you know. Thanks Cat
Super User
Super User
Posts: 6,383

Re: How do I create 'dynamic' sql in amarco? SAS 9

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;
Valued Guide
Posts: 2,174

Re: How do I create 'dynamic' sql in amarco? SAS 9

If the collection of columns is mixed types (chars and nums) the --range of names has a very convenient extension

AB1_AccountID -character- AB3_Address
AB1_AccountID -numeric- AB3_Address
This extension of the -- range segregate columns by data type.
It does not matter if the "end-points" of the name range are of the "wrong" type - they would be ignored just as wrong datatypes within the range would be ignored.
Valued Guide
Posts: 2,174

Re: How do I create 'dynamic' sql in amarco? SAS 9

Hi Cat
Did you build a solution?
Ask a Question
Discussion stats
  • 5 replies
  • 158 views
  • 0 likes
  • 4 in conversation