How to use a table instead of macro variable

[Aexor]

Hi ,

I need your help and suggestion in one of issue.

I am creating a macro variable

proc sql;

select xyz into  : abc separated by ',' from large_data;

quit;

I want to use all the values coming in abc macro variable in later steps.

Due to large number of observation the I am getting error while using this "abc"  macro variable .

Need your help to  understand what else I can do here so I can use all the values present in abc in further steps.

[SASKiwi]

Please provide more details on how you are using this macro list. Without it we can't provide a better solution. Are you selecting data from an external database for example?

[PaigeMiller]

You should not tell us you get an error without showing us the LOG. We need to see the ENTIRE log for the step (data step or proc) that has the error. Copy the log as text and paste it into the window that appears when you click on the </> icon.

[Patrick]

There could be multiple reasons for ending up with errors. You will need to provide more detail like how you are using this macro variable, the relevant log portion with the error, the number and average length of distinct values used to populate the macro variable. 

Potential issues:

1. Too many values to fit into a single macro variable

2. If you want a distinct list of values then you need to add a DISTINCT to your select clause (and then the values might fit into a single macro variable).

3. If the source variable is of type character then you need to add quotes around the values.

Code you could try for a character variable as source:

proc sql;
  select distinct cats("'",xyz,"'") into :abc separated by ',' 
  from large_data
  ;
quit;

But you still need to ensure that this list fits into a single macro variable.

As per documentation a macro variable can contain 64KB. I always implement for a max size of 30KB to avoid any conflict with other limitations like the max size of a SQL query one can send to a database, the max size of a SAS variable etc.

...and given the subject line: Yes, using a table is eventually the right thing to do but we do need to know what you are trying to do. Is this large table used for a join, or the values are used to subset another big table ...or what. 

How many distinct values and average length are you actually dealing with. What do you get running below query?

proc sql;
  select 
    count(distinct xyz) as n_dist_values 
    ,avg(length(cats(xyz))) as avg_len
  from large_data
  ;
quit;

[Aexor]

Thank you. Distinct value is around 6 thousand, and each value is of length 30 char

[Tom]

---

@Aexor wrote:
Thank you. Distinct value is around 6 thousand, and each value is of length 30 char

---

So 30 chars means it is character and not numeric. So you need 33 bytes per value (or more if the values contain quotes) which means 198,000 bytes total.

Since the maximum length of an actual variable is 32K you would need about 6 to 7 lists.

So if dataset HAVE has a list of distinct values that is each 30 bytes long or less then you could use a data step like this:

%let list=;
data _null_;
   mvar+1;
   do item=1 to 900 until(eof);
     set have end=eof;
     length string $32767;
     string=catx(',',quote(trim(VALUE),"'"));
   end;
   call symputx(cats('list',mvar),string);
   call symputx('list',catx(',',symget('list'),cats('&list',mvar));
   call symputx('list_n',mvar);
run;

to make a series of macro variable named LIST1, LIST2, etc that each had up to 900 quoted values separated by commas.

And also two extra macro variables. One name LIST_N with the count and one named LIST that references all of the others.

You could then use just the one macro variable in your code. Like:

where VARNAME in (&list)

And it will expand to the full list of 6,000 values.

Of course the statement might be too long to process.  In which case you will need to use the LIST_N macro variable to loop over the shorter lists.

%do index=1 to &list_n ;
   ...
   where VARNAME in (&&list&index)
   ...
%end;

[ErikLund_Jensen]

Hi @Aexor 

I have met the same problem several times,  so now I always use a combination of a table with distinct values and a macro loop. 

/* Get distinct list of different values */
proc sql;
  create table mylist as
    select distinct xyz
    from large_data;
quit;

/* Macro to loop over list, get next value in list and do something based on the value */
/* Payload inside the %do loop could be any ny number of steps */
%macro loopmylist;

  /* loop over list */
  %do i = 1 %to &sqlobs;

    /* Get next value from mylist and store it in macro variable */
    data _null_;
      set mylist (firstobs=&i obs=&i);
      call symputx('thisvalue',xyz);
    run;

    /* Payload code - Do whatever should be done based on the actual value */
    /* This is an example */
    Proc sql;
      create table subset_&xyz as
        select *
        from large_data
        where xyz = "&thisvalue" /* quotes only in case of char values */
    quit;
    /* End of Payload */
  %end;
%mend;
%loopmylist;

[Aexor]

Thank you! I will give it a try. Also good to know that is problem is known. We can find some solution. 

[Aexor]

Hi @Erik , I have tried this, but issue is I need list of distinct values to be used in later step. I can't use those steps in loopmylist

[ballardw]

How you use the values does matter.

[svh]

I'm not sure I fully understand the question, but here are some ways that I use macro variables in horizontal lists, which is what that first PROC SQL statement is doing in your example. Lists of macro variables are really great things in SAS because you can use much less code to produce more charts or reports or other things. The key is being able to iterate over that list to get the output you want.  When you have a horizontal list of macro variables, you can then iterate over that list with the %SCAN function. In this example, a horizontal list of macro variables (one value for each distinct value of STOCK in the sashelp.stocks data set) is used to plot multiple charts. I found this example somewhere...

%macro graph_stocks;
proc sql noprint;
   select distinct stock into :STOCK_LIST separated by '~'
   from sashelp.stocks;
   %let NUM_STOCKS = &sqlobs; /*This &NUM_STOCKS macro variable will store the number of macro variables in your horizontal list*/
quit;

%do I = 1 %to &Num_Stocks; /*Initialize the %DO loop for iterating over your list of macro variables*/
ods pdf file = "%scan(&STOCK_LIST, &I, '~').pdf"; 
proc sgplot data=sashelp.stocks;
where stock = "%scan(&STOCK_LIST, &I, '~')";
highlow x=date high=high low=low;
run;
ods pdf close;
%end;
%mend;
%graph_stocks();

In your message, you also talk about using a 'table' and not a macro variable. You can also make VERTICAL lists of macro variables in this manner below. Note that you do not use the SEPARATED BY syntax here. You will get back n macro variables for every n distinct levels of ORIGIN that exist.

proc sql noprint;
select distinct origin
into :origin1- /*one macro variable is created for each distinct level of the the ORIGIN variable in SASHELP.CARS*/
from sashelp.cars
order by origin;
%let numorigins = &sqlobs;
quit;
%put &=origin1;
%put &=origin2;
%put &=sqlobs;

[Aexor]

Thanks for your response. The issue is the distinct value , I need to use all in later steps. This distinct table won't be used later. I need to use list of values all together in later steps

[Patrick]

---

@Aexor wrote:
Thanks for your response. The issue is the distinct value , I need to use all in later steps. This distinct table won't be used later. I need to use list of values all together in later steps

---

Your statements above might be true for how you currently designed but that doesn't mean there aren't other easier to implement ways for doing this. 

Please show us how you intend to use this list of distinct values. For example if they are used in a datastep in code like if <variable> in (&value_list) then another option would be a hash lookup table.

[Aexor]

Thank you all for your response. I can not post my log here, due to privacy issue. I can't copy those datasets in my local .

Coming to the solution part, As I didn't get any solution, I am trying to replace the complete macro step with a table cretion 

e.g 

proc sql;

select xyz into  : abc separated by ',' from large_data;

quit;  

is replaced with

proc sql;

create table temp as

 select distinct xyz from large_data

quit;

Thanks!

[ballardw]

---

@Aexor wrote:

Thank you all for your response. I can not post my log here, due to privacy issue. I can't copy those datasets in my local .

Create data sets with different variable names and non-sensitive information that behaves like your real data.

Demonstrate the use with the dummy data.

Make sure you can determine the desired result from your dummy data and show that.