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
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.
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.
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
@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;
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.
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;
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) ;
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);
"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).
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.