A macro to transpose all datasets from a given library

[chris2377]

Dear all

I have a number of SAS datasets stored in a library. I want to transpose each file in the same way. I use the following macro code:

options mprint mlogic;
%macro libloop;

/* List of variables to loop through*/
   proc sql noprint;
      select distinct memname
         into :dslist separated by ' '
         from sashelp.vmember
         where memtype='DATA' and libname='BS'
         order by memname;
   quit;

/*A loop to transpose all datasets*/

%let i=1;
%do %while(%scan(&dslist,&i) ne );

		proc sql; /* HERE IS PROBABLY A PROBLEM*/
			select distinct name into :vars separated by ' '
			from dictionary.columns
			where libname='BIBL' and memname='%scan(&dslist,&i)' 
and label not in ('label of var 1', 'label of var 2');
		quit;


		proc sort data=bs.%scan(&dslist,&i);
				by var1 var2;
		run;
		proc transpose data=bs.%scan(&dslist,&i)  out=bs.t_%scan(&dslist,&i);
			by var1 var2;
			var &vars; /*SAS does not recognize the variable &vars*/
		run;

	
%let i=%eval(&i+1); 

%end;


%mend libloop;

%libloop;

The problem is that the macro does not recognize the macro variable &vars that I create with the second proc sql (I get an error "ERROR: Variable VARS not found"). How to do it right?

[RW9]

The reason it doesn't recognise the list, is due to the nature of macro.  When a program is submitted, it is sent to the macro pre-processor.  This tool expands all the macro parts of the code into full Base SAS code.  That full SAS code is then sent to the compiler for execution.  Now in your code, you have a procedure proc sql which is in turn generating macro, but at the time the pre-processor works, this isn't available.  You end up in a nasty back and forth scenario.  

Firstly I would, as always question why you want to transpose loads of datasets, rarely other than for review output is three any benefit to using a transposed dataset.  

Secondly if you have lots of data that require exactly the same process, the question has to be asked why the data is all split up.  Again there is rarely, if ever, any benefit to splitting data that is the same up into lots of different blocks.  All that code you have posted below is cause purely by splitting the data up.  If it was not split up, then a simple 1 step proc tranpose would be effective.

So, I would suggest to rethink your process.  Put all of the data - which is all the same - into one dataset, you can keep a dataset name if you need to, I know in finance they are chained to the notion of dates in filenames for instance.  Something like:

data all;
  set work.file_: indsname=tmp;
  length myfname $200;
  myfname=tmp;
run;

proc sort data=all;
  by ...;
run;

proc transpose data=all;
  ...
run;

[chris2377]

Why do I need to transpose? To make long story short - because I have external data in a wrong format. To be more precise: I have a data that are in the "wide" format, eg.:

data wide;
input id sales2010 sales2011 no_cust2010 no_cust2011;
datalines;
1 100 110 10 15
2 200 210 20 25
;
run;

What I need is a data in the "long" format (panel dataset):

data long;
input id year sales no_cust;
datalines;
1 2010 100 10
1 2011 110 15
2 2010 200 20
2 2011 210 25
;
run;

To do this I need to transpose the original dataset.

The original datasest are quite big (around 20 thousand records an around 1 thousand variables each), so don't even think about combining them

But apart from my motivation - why is creating a macro variable inside a macro via proc sql does not work in my code?

[ballardw · Accepted solution]

1) If you are going to code macros then you need to learn to use Options MPRINT and SYMBOLGEN to examine code generated by your macro

2) You also need to pay very close attention to choice of quote marks in code:

memname='%scan(&dslist,&i)'

Use of single quotes means that the macro functions and variables inside the quotes to NOT resolve.

Your log should have shown you a number of messages about 0 records selected from dictionary.columns.

Try

memname= "%scan(&dslist,&i)"

But I think that creating a separate macro variable
   %let Dname = %scan(&dslist,&i);

before the second proc sql call.

and using &Dname instead of repeated calls to %scan(&dslist,&i) might be a more robust choice in the future since you are using the same call 4 different places.

[chris2377]

Thank you. After changing quotation marks from ' to " everything works.

I missed the option symbolgen, thanks for pointing this out.