BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

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

10 REPLIES 10
Reeza
Super User

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.

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASAna
Quartz | Level 8

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

Tom
Super User Tom
Super User

@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;
SASAna
Quartz | Level 8

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.

Reeza
Super User

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;
Quentin
Super User

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) ;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Reeza
Super User

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);
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Tom
Super User Tom
Super User

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;

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
  • 10 replies
  • 1738 views
  • 2 likes
  • 5 in conversation