Defining the number of variables in a transpose macro

windy — Sat, 09 May 2020 04:59:02 GMT

Hi all,

Please help me to solve this problem.

I want to run a macro to perform the transpose for many datasets. However, the number of variables that need to be transposed is slightly different across datasets. It could be 39, 40 or 41. Suppose I define those variables as VAR1, VAR2, etc.

My macro for transpose is like:

%Macro transpose;

%do i=1999 %to 2018;

proc transpose data=have_&i out=want_&i;

var var1-var40; /*the number of vars is different across each dataset (in each year)*/

run;

%end;

%mend transpose;

I created a dataset below to have the number of variables I need to transpose in each year:

data num_var;
input year num_var;
cards;
2018 40
2017 40
2016 40
2015 40
2014 39
2013 39
2012 39
2011 39
2010 40
2009 39
2008 39
2007 39
2006 41
2005 41
2004 40
2003 40
2002 40
2001 40
2000 39
1999 39
;
run;

For each dataset (in each year), how can I use the value in the dataset num_var to define the VAR option in my transpose macro?

Many thanks in advance.

Re: Defining the number of variables in a transpose macro

Kurt_Bremser — Sat, 09 May 2020 06:28:12 GMT

Simply use a wildcard in the VAR statement:

var var:;

Re: Defining the number of variables in a transpose macro

windy — Sat, 09 May 2020 06:45:21 GMT

Oh, thank you. It's so simple. I was using PROC SQL and a lot of other stuff. I just make thing complicated.

topic Re: Defining the number of variables in a transpose macro in SAS Procedures

Defining the number of variables in a transpose macro

Re: Defining the number of variables in a transpose macro

Re: Defining the number of variables in a transpose macro