DATA Step, Macro, Functions and more

Creating macro variable for a set of attributes

Reply
Frequent Contributor
Posts: 124

Creating macro variable for a set of attributes

Hi SAS Users,

 

I am looking for some suggestions on creating the macro variable for 15 variables of the total 400 variable table.

 

Below is my thinking , but if there is a better way, please suggest me.

 

proc sql noprint;            
  select lowcase(name) into :tnames separated by ','         
  from sashelp.vcolumn       
  where libname = 'WORK'     
  and upcase(memname) = "CLMS"   ---  CLMS is the table has 400 attributes
  order by lowcase(name)     
  ;          
quit;  

 

the fields that are needed to be converted to macro are -

 


         Proc Sql Noprint;
            Select func_1 func_2 func_3 func4 ..............................func15
                          Into:func_1 func_2 func_3 func4 ..............................func15
            From CLMS
          Quit;

 

Thanks,

Ana

Super User
Posts: 23,997

Re: Creating macro variable for a set of attributes

Its not quite clear how many macro variables you're trying to create. 

 

Because you SQL doesn't support variable lists or arrays this is likely much easier in a data step using CALL SYMPUTX


If you can clarify what you're trying to do we can help you with sample code.

PROC Star
Posts: 1,471

Re: Creating macro variable for a set of attributes

Agree with Reeza.  If you show 5 records from your CLMS dataset, and then describe the output you would want (creation of a bunch of macro variables?), that would help.  You don't  need to show real data, just a few variables (func_1-func_3) and a few records should be enough.

Frequent Contributor
Posts: 124

Re: Creating macro variable for a set of attributes

Hi Reeza,

 

Thanks for the reply.

 

CLMS is the oracle table that has 400 attributes. Data will be something like this

 

attributes - > test1    test2   test3   test4  ....................................................func1  func2 func3 ........func15 ................................test400

data         --> a          b        c         d        ----------------------------------------   1         0         1     -----    0         ------------------------   d

 

func1 to func15 are the numeric attributes that has 1 or 0 values.  I need to calculate the sum by line level in my code. So i was trying create macro variable and use sum function.

 

Thanks,

Ana

Super User
Super User
Posts: 8,273

Re: Creating macro variable for a set of attributes


SASAna wrote:

Hi Reeza,

 

Thanks for the reply.

 

CLMS is the oracle table that has 400 attributes. Data will be something like this

 

attributes - > test1    test2   test3   test4  ....................................................func1  func2 func3 ........func15 ................................test400

data         --> a          b        c         d        ----------------------------------------   1         0         1     -----    0         ------------------------   d

 

func1 to func15 are the numeric attributes that has 1 or 0 values.  I need to calculate the sum by line level in my code. So i was trying create macro variable and use sum function.

 

Thanks,

Ana


To get sums just use PROC SUMMARY.  You didn't say what your id variables are but let's just assume they are LINE and LEVEL.  You could a BY statement if your data is sorted (or indexed in Oracle).  You can list the variables you want in the VAR statement. Or you could use SQL code to select a space delimited list of variable names.

proc summary data=oralib.tablename nway ;
  class line level ;
  var func1-func15 ;
  output out=want sum= ;
run;
Frequent Contributor
Posts: 124

Re: Creating macro variable for a set of attributes

Thanks for the answers.

 

Attributes are in different names to put them in var part of the proc summary.

 

so, i was thinking to create them as Macro variables and use it with summary function.

 

Thanks again,

Ana.

Super User
Posts: 23,997

Re: Creating macro variable for a set of attributes

So is this a metadata table of sorts, that lists the variables and you're trying to extract variable names from it?

 

That's perfectly valid approach, my only suggestion would be to transpose the data so you could use SQL to create the macro variables instead, for example this creates a macro variable of the list of numeric variables in the CARS data set. 

 

proc sql noprint;
select name into :var_list separated by " "
from sashelp.vcolumn
where libname='SASHELP' and memname='CARS' and type='N';
quit;

(You could just use _numeric_ instead though). 

 

proc summary data=sashelp.cars;
var _numeric_;
run;
PROC Star
Posts: 1,471

Re: Creating macro variable for a set of attributes

You don't need a list of variables for the SUM function. It will accept a variable list, so you can do something like:

mysum=sum(of func1-func15) ;
Super User
Posts: 23,997

Re: Creating macro variable for a set of attributes

Not clear why you need macro variables, your post didn't come across clearly, can you please reformat that so it's legible, I can't follow it Smiley Sad

 

The logic doesn't make sense though, the sum by line is easily done using the sum function.

 

x=sum(of func1-func15);
Super User
Super User
Posts: 9,829

Re: Creating macro variable for a set of attributes

"suggestions on creating the macro variable for 15 variables of the total 400 variable table" - I would suggest to remodel your data.  400 variables is a lot, and starting to push processing into macro is only going to make your code far more overcomplicated, and less robust.  This is something seen here a lot, and generally comes from an Excel way of thinking.  You can work with small clumps of variables simply by using an array.  However a far more robust, simple to code, and easier to read/maintain method is to normalise your data, so rather than have 400 variables, have 10 or 20 id variables, then have a paramter/response setup, e.g.:

I have:

ID1  ID2   Q1   Q2   Q3  Q4

1     1       abc  def   ghi  rty

 

I remodel this as:

ID1  ID2  Q   RESPONSE:

1      1     Q1 abc

1      1     Q2  def

1      1     Q3  ghi

...

 

There are many reason why this is a better model, first off the number of variables you have to work with (essentially hardcode in your program) is minimal, the data can expand without re-coding.  Second you can still do processing on a select number of data points, by simply using a where Q="Q2" for instance, far simpler than macro lists, and loops trying to do the same.  You will also find your data tends to be smaller on the disc as only the necessary information is kept (whereas as large oblong of data will generally hold lots of missing data).

Super User
Super User
Posts: 8,273

Re: Creating macro variable for a set of attributes

Your code is almost there, but the syntax for the INTO clause is a little different from the SELECT clause.

proc sql noprint;
  select name
       , ':'||name
    into :varnames separated by ','
       , :mvarnames separated by ' trimmed ,'
  from sashelp.vcolumn
  where libname = 'WORK'
    and upcase(memname) = "CLMS"
  ;

So now you can use the macro variables to generate your SQL statement.

  select &varnames into &mvarnames from CLMS;
quit;
Ask a Question
Discussion stats
  • 10 replies
  • 187 views
  • 2 likes
  • 5 in conversation