DATA Step, Macro, Functions and more

Insert Values into an array variable for further use

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Insert Values into an array variable for further use

Hi,

 

I have a table with 3 columns/variables and I want to insert these values into an array variable.

 

Table_Name Column_Name Frequency

asd               sjd                      5

jdk                dda                     8

 

What I want is to select one row as a variable to be used at a later time, like &Table_Name , &Column_Name and &Frequency.

 

The code might be something like this: 

 

Proc sql;
Select Table_name into: Table_name from (tablename);
Select Clumn_name into: Column_name from (tablename) where &table_name=asd;

Regards,

Sarthak Kataria


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,280

Re: Insert Values into an array variable for further use

Posted in reply to katariasarthak

There are no arrays in the macro language.

 

That said,

%let table_name=asd;

Proc sql;
Select Column_name into: Column_name from tablename where table_name = "&table_name";
select frequency into :frequency from tablename where table_name = "&table_name";
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 10,280

Re: Insert Values into an array variable for further use

Posted in reply to katariasarthak

There are no arrays in the macro language.

 

That said,

%let table_name=asd;

Proc sql;
Select Column_name into: Column_name from tablename where table_name = "&table_name";
select frequency into :frequency from tablename where table_name = "&table_name";
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

Re: Insert Values into an array variable for further use

Posted in reply to KurtBremser

Hi,

 

This code will work great for the first row, but can there be a macro code or can we use a do loop to automatically select table_name for every row? 

 

Thanks!

Super User
Posts: 10,280

Re: Insert Values into an array variable for further use

[ Edited ]
Posted in reply to katariasarthak

You can put lists of values into macro variables, eg

proc sql noprint;
select name, sex into :name separated by ",",:sex separated by ',' from sashelp.class;
quit;

%put names=&name;
%put sexes=&sex;

The question is: what will you do afterwards with that? Instead of using long macro variables (which won't scale to very large lists), it is better to keep the dataset as source and use call execute in a data step to dynamically create code according to what is needed:

 

data control;
input table_name :$41. var_name :$32. group_name :$32.;
cards;
sashelp.class weight sex
sashelp.cars horsepower origin
;
run;

data _null_;
set control;
call execute('proc means data=' !! trim(table_name) !! '; class ' !! trim(group_name) !! '; var ' !! trim(var_name) !! '; run;');
run;

 This will scale well beyond the 64K limit of macro variables.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 580

Re: Insert Values into an array variable for further use

Posted in reply to katariasarthak

Don't move data into macro-variables, this is seldom necessary and creates more trouble when using those variables. Another problem: array-variables only exists in data-step-code, so you need one macro variable per observation/data-step-variable.

 

If you explain what you want to do with those macro-variables, i am sure that somebody has an idea not relying on macro-variables.

Super User
Super User
Posts: 8,127

Re: Insert Values into an array variable for further use

Posted in reply to katariasarthak

I don't see how this has anything to do with arrays.

It looks like you have basic idea of how to use PROC SQL to create macro variables from data values. You would be more efficient to create all of the macro variables from the same query.

%let table_name = asd ;
proc sql noprint;
select Column_name
     , Frequency
  into :Column_name trimmed
     , :Frequency trimmed
  from mytable
  where Table_Name = "&table_name"
;
quit;
Occasional Contributor
Posts: 17

Re: Insert Values into an array variable for further use

Hi,



This code will work great for the first row, but can there be a macro code or can we use a do loop to automatically select table_name for every row?



Thanks!
Occasional Contributor
Posts: 17

Re: Insert Values into an array variable for further use

[ Edited ]
Posted in reply to katariasarthak

This code worked perfectly for me Smiley Happy

 

 

%macro var();
 
proc sql noprint;
select count(*) into: Count from control_table_multiple;
quit;

proc sql noprint;
select Table_Name into: table_name separated by ',' from control_table_multiple;
quit;
 
%do i=1%to &Count;
%let tab_name= %scan(%bquote(&table_name),&i,%str(,));
 
proc sql noprint;
select Column_Name into: col_name from control_table_multiple where Table_Name= "&tab_name";
select Frequency into: freq from purge.control_table_multiple where Table_Name= "&tab_name";
quit;

%end;
%mend var; %var;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 117 views
  • 1 like
  • 4 in conversation