BookmarkSubscribeRSS Feed
Cathryn
Calcite | Level 5

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

5 REPLIES 5
art297
Opal | Level 21

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

Cathryn
Calcite | Level 5
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
Tom
Super User Tom
Super User

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;
Peter_C
Rhodochrosite | Level 12
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.
Peter_C
Rhodochrosite | Level 12
Hi Cat
Did you build a solution?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 607 views
  • 0 likes
  • 4 in conversation