BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
katariasarthak
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

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;
katariasarthak
Obsidian | Level 7

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!

Kurt_Bremser
Super User

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.

andreas_lds
Jade | Level 19

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.

Tom
Super User Tom
Super User

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;
katariasarthak
Obsidian | Level 7
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!
katariasarthak
Obsidian | Level 7

This code worked perfectly for me 🙂

 

 

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

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
  • 7 replies
  • 4038 views
  • 1 like
  • 4 in conversation