Sas dynamic call symput with unknown number of fields in dataset
I have the following dataset
data parm2;
input a b c d e;
datalines;
1 2 3 4 A
;
run;
Problem1: I need a set of macro variables. Suppose I don't know the number of fields and the corresponding field name.
Problem2: the fields are not the same data types.
the desired operation looks like this:
data _null_;
set parm2;
call symput('a',a);
call symput('b',b);
call symput('c',c);
call symput('d',d);
call symput('e',e);
run;
%put &a;
source to share
after some research I found the following solution. While not ideal, it's worth sharing. Waiting for @Reeze's response
data _null_;
set parm2;
array t(*) _numeric_; /*this deal with different data type*/
do i = 1 to dim(t);
call symput(vname(t[i]), t[i]);
end;
array t2(*) _character_;
do i = 1 to dim(t2);
call symput(vname(t2[i]), t2[i]);
end;
run;
source to share
Here's a VNEXT solution with VVALUEX called, assuming you don't have a variable that has the same name as the automatic variable that seems to work. Derivative solution from SAS Note: http://support.sas.com/kb/24/798.html
data parm2;
input a b c d e $;
datalines;
1 2 3 4 A
;
run;
data _null_;
set parm2;
length name $32;
*temporarily set name to not missing to start loop;
name='blank';
do while(name ne " ");
call vnext(name);
/* Omit automatic variables, and variables created in this step only */
if trim(name) not in('list','name','flag','i',' ','_ERROR_','_N_') then
call symput(name, vvaluex(name));
end;
run;
%put &a;
%put &b;
%put &c;
%put &d;
%put &e;
source to share
[Edited - some codes or lines of code are marked with * as OP does not require it]
Use a dictionary proc sql
to get the variable name contained in your database using the Memname
and specifications libname
.
Use data step
to get the variables in the marco variable. The variable name is stored under the column name name
and therefore we have to specify it as call symputx( 'variable ' !! left(_n_), **name** );
. The macro variable function Total
is to specify the number of variables that existed in your dataset.
Now you will have variable1 = a, variable2 = b ....
%macro definevar ( library, dataset);
proc sql;
create table Attribute as
select * from dictionary.columns;
where memname = upcase( &dataset ) and libname = upcase(&library);
quit;
data letmacro;
set Attribute end=end;
call symputx( 'variable ' !! left(_n_), name );
* if end then call symputx ( Total, _n_);
run;
/*
***** extra ********
data _null_;
set &dataset ;
%do i=1 to &total;
call symputx ( "var&i" !! left(_n_), &&variable&i );
%end;
run;
***** extra ********
*/
%mend definevar;
%definevar( ifanylibrary, parm2)
And I am looking forward to learning the CALL VNEXT solution from @Reeza
source to share