I have bunch of character values that I need to use to filter data. Below is my code.
select distinct abc
into :def separated by ","
where abc is not null;
/* making a macro */
Below is what I get.
2 The SAS System 10:26 Tuesday, June 28, 2016
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
As you can see, it does not put "," after the value '00002659' I know it has something to do with the length of the string. but do not know what to do with it.
Your variable value already includes the quotation marks? You can include a space after your comma in the separated by clause.
Typically to create a macro variable list from a character value I'll use the following code
proc sql; select quote(name) into :name_list separated by ", " from sashelp.class; quit;
The data step does nothing useful and it is probably what is generating message about have a long quoted string since you coded "&def". If you want to see the values in the log use %PUT statement, no needs for wrapping the value in double quotes.
proc sql noprint; select distinct abc into :def separated by "," from work.table1 where abc is not null ; %put NOBS = &sqlobs ; %put LENGTH = %length(&def) ; create table subset as select * from table2 where abc in (&def) ; quit;
There is a limit to the length of a macro variable (65K) and it is acutally longer than the limit for the length of a data set character variable (32K). The truncation might be caused by the use of the data step. But if your list of values is that long then you might not use this method as your list only needs to get twice as long to be too long to store in a single macro variable. Looks like your values are all 8 characters (plus 2 single quotes and a comma) so 11 characters per value. So you the maximum number of values you could put into a macro variable is less 6,000.
Why not just use the values directly from the TABLE1 instead of making a macro variable?
proc sql noprint; create table subset as select * from table2 where abc in (select abc from table1 where abc is not null) ; quit;
Hi Tom, thanks for your reply. Below is what my data looks like in the work.table1. Some rows do not have values that's why I am saying where abc is not null. I am trying to put all of them in one string. In your reply above, I could not figure out where s table2 coming from.
You're using the values in the macro variable to filter another query, correct?
Table2 is the table in the query where you would use the macro variable.
In general, rather than create a macro variable you could use a subquery. Given your data structure that isn't possible.
It's almost too bad that you have quotations and comma's in your variables already, that makes the issue harder. Did you create those variables? If so, is it possible to back up a step and do it differently?
Yes, I am planning to use the macro variable to filter data in another query.
The data came to me as I showed you. with the quotes and commas. There are 500+ lines with that kind of values. I can remove quotes and commas and edit the table so there would be one value for ABC in each row but that means 500+ rows will be converted to 5000+, which fine but it will take a lot of work to seperate them.
Do you think that is the only resolution?
It is not hard to convert your TABLE1 into a normal looking table. Let's make an example using the values you posted.
data table1 ; input abc $70. ; cards; '00004014','00003931','C0003930','00004821' '00004426','00004427','C0004425' '00000601','C0000600' '00003027','00000603','C0000602' ;
Now let's convert it.
data fixed ; set table1; length value $8 ; do i=1 to countw(abc,",'"); value = scan(abc,i,",'"); output; end; run;
Now you could use the table in a query. Let's assume you have TABLE2 that looks like:
data table2 ; length id 8 value $8 ; input id value ; cards; 1 00004014 2 00003931 3 C0003930 4 00004821 5 00004426 6 00004427 7 C0004425 8 XXXXYYYY 9 00001111 ;
Now you can use FIXED to subset TABLE2 in many ways.
proc sql ; create table subset1 as select * from table2 where value in (select value from fixed) ; quit;
data subset2 ; merge table2 (in=in1) fixed(in=in2); by value; if in1 and in2; run;
Or using your macro varible method (remember this won't work if you have more that about 5,950 distinct values).
proc sql noprint ; select distinct quote(trim(value)) into :list separated by ',' from fixed ; create table subset3 as select * from table2 where value in (&list) ; quit;
I thought it was all set but I just noticed that the macro DEF does not put a "," after one of the values. so, back to square one. This happens even after I seperated ABC values. The reason I need to create macro to use to filter in another query is that query is a pass through query where I can not use the local table that has ABC values. I am so sorry for keep coming back.
proc sql noprint ;
select distinct quote(abc,"'")
into :def separated by ','
where abc is not null;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.