BookmarkSubscribeRSS Feed
sasmom
Fluorite | Level 6

I have bunch of character values that I need to use to filter data. Below is my code.

 

proc sql;

select distinct abc

into :def separated by ","

from work.table1

where abc is not null;

quit;

/* making a macro */

data _null_;

call symput('test',"&def.");

run;

 

Below is what I get.

 

'C0003923','00003953','C0003952','00004014','00003931','C0003930','00004821','00004426','00004427','C0004425','00

004429','00004430','C0004428','00004621','00001953','00001954','C0001952','00005850','00005851','C0005849','C0002034','0

2 The SAS System 10:26 Tuesday, June 28, 2016

0002035','C0002048','00002049','C0002306','C0002658','00002659'

WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation

marks.

 

0002035','C0002048','00002049','C0002306','C0002658','00002659'

'00000302','00000301','C0000300','00000418','00000417','C0000416','00002145',....

 

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.

 

Please help.

 

Thanks

 

 

16 REPLIES 16
Reeza
Super User

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;
Reeza
Super User

Also, the data _null_ step is redundant at this point, it's reassigning the value of DEF to TEST, which doesn't serve much of a purpose.

 

 

Tom
Super User Tom
Super User

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;
sasmom
Fluorite | Level 6

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.

 
 
'00004014','00003931','C0003930','00004821'
 
 
 
'00004426','00004427','C0004425'
 
'00000601','C0000600'
'00003027','00000603','C0000602'
Reeza
Super User

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?

sasmom
Fluorite | Level 6

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?

Reeza
Super User

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. 

 

%put &DEF.;

 

Tom
Super User Tom
Super User

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;

Or

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; 

 

sasmom
Fluorite | Level 6

Thanks Tom.

 

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?

 

Reeza
Super User

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. 

sasmom
Fluorite | Level 6

Thanks a lot Reez and Tom. I can not believe how easily it seperated the values for ABC.

 

IT WORKED.

 

 

sasmom
Fluorite | Level 6

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.

 

Thanks

 

proc sql noprint ;

select distinct quote(abc,"'")

into :def separated by ','

from table1

where abc is not null;

quit;

%put &def.;

 

'C0001988','C0002048','C0002063','C0002144','C0002244','C0002246','C0002272','C0002286','C0002526','C0003232','C00

03837','C0003930','C0004425','C0004428','C0004917','C0004920','C0005011','C0006466'

'00000301','00000302','00000417','00000418','00000601','00000603','00000887','00000888','00000896','00001113','00001114','00001116',

 

Reeza
Super User

Can you double check that record in the data to verify it was parsed out properly. 

sasmom
Fluorite | Level 6

that was my first reaction. Below is what it looks like in the table. Nothing different than other numbers.

 

00003027
C0000602
 
 
00001259
C0001258
C0006466
00002049
C0002048
00002064
C0002063

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!

What is Bayesian Analysis?

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.

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
  • 16 replies
  • 1540 views
  • 2 likes
  • 3 in conversation