topic Re: Creating macro variable for a set of attributes in SAS Programming

Creating macro variable for a set of attributes

SASAna — Mon, 29 Jan 2018 16:12:14 GMT

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

Re: Creating macro variable for a set of attributes

Reeza — Mon, 29 Jan 2018 16:28:05 GMT

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.

Re: Creating macro variable for a set of attributes

RW9 — Mon, 29 Jan 2018 16:34:01 GMT

"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).

Re: Creating macro variable for a set of attributes

Quentin — Mon, 29 Jan 2018 16:38:37 GMT

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.

Re: Creating macro variable for a set of attributes

Tom — Mon, 29 Jan 2018 16:52:56 GMT

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;

Re: Creating macro variable for a set of attributes

SASAna — Mon, 29 Jan 2018 16:53:23 GMT

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

Re: Creating macro variable for a set of attributes

Tom — Mon, 29 Jan 2018 16:57:00 GMT

@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;

Re: Creating macro variable for a set of attributes

Quentin — Mon, 29 Jan 2018 16:57:29 GMT

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) ;

Re: Creating macro variable for a set of attributes

Reeza — Mon, 29 Jan 2018 16:57:32 GMT

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 😞

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

x=sum(of func1-func15);

Re: Creating macro variable for a set of attributes

SASAna — Tue, 30 Jan 2018 03:19:52 GMT

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.

Re: Creating macro variable for a set of attributes

Reeza — Tue, 30 Jan 2018 03:35:16 GMT

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;