06-28-2016 05:52 PM
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.
06-28-2016 06:44 PM
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;
06-28-2016 07:41 PM
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;
06-28-2016 09:05 PM
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.
06-28-2016 09:09 PM
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?
06-28-2016 09:23 PM
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?
06-28-2016 09:35 PM
No. Your original query is fine.
proc sql noprint; select distinct abc into :def separated by ", " from work.table1 where not missing(abc); quit;
Your data _null_ step is not required.
Try using the macro variable DEF as is.
06-28-2016 10:53 PM - edited 06-28-2016 10:54 PM
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;
06-29-2016 02:37 PM
I have not had a chance to try out your method to fix the table. The table, that has the column that I sent, has 20 additinal fields/columns. There are 500+ rows. Would your method work for that table?
06-29-2016 02:42 PM
Given your macro variable only deals with one column you can drop the remaining columns and the number of observations is not relevant. 500 rows is trivial to SAS.
06-29-2016 06:30 PM
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;
06-29-2016 07:08 PM
that was my first reaction. Below is what it looks like in the table. Nothing different than other numbers.