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

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

Cathryn — Wed, 15 Feb 2017 22:49:39 GMT

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

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

art297 — Wed, 15 Feb 2017 23:59:56 GMT

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

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

Tom — Thu, 16 Feb 2017 01:45:59 GMT

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;

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

Peter_C — Fri, 17 Feb 2017 09:21:32 GMT

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.

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

Cathryn — Wed, 22 Feb 2017 16:48:04 GMT

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

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

Peter_C — Thu, 20 Apr 2017 08:39:42 GMT

Hi Cat
Did you build a solution?