topic Re: Creating macro variable in SAS Procedures

Creating macro variable

sasmom — Tue, 28 Jun 2016 21:52:22 GMT

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

Re: Creating macro variable

Reeza — Tue, 28 Jun 2016 22:44:03 GMT

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;

Re: Creating macro variable

Reeza — Tue, 28 Jun 2016 22:45:07 GMT

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.

Re: Creating macro variable

Tom — Tue, 28 Jun 2016 23:41:27 GMT

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;

Re: Creating macro variable

sasmom — Wed, 29 Jun 2016 01:05:45 GMT

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'

Re: Creating macro variable

Reeza — Wed, 29 Jun 2016 01:09:07 GMT

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?

Re: Creating macro variable

sasmom — Wed, 29 Jun 2016 01:23:05 GMT

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?

Re: Creating macro variable

Reeza — Wed, 29 Jun 2016 01:35:09 GMT

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

Re: Creating macro variable

Tom — Wed, 29 Jun 2016 02:54:43 GMT

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;

Re: Creating macro variable

sasmom — Wed, 29 Jun 2016 18:37:51 GMT

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?

Re: Creating macro variable

Reeza — Wed, 29 Jun 2016 18:42:09 GMT

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.

Re: Creating macro variable

sasmom — Wed, 29 Jun 2016 19:08:06 GMT

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

IT WORKED.

Re: Creating macro variable

sasmom — Wed, 29 Jun 2016 22:30:22 GMT

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',

Re: Creating macro variable

Reeza — Wed, 29 Jun 2016 22:32:39 GMT

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

Re: Creating macro variable

sasmom — Wed, 29 Jun 2016 23:08:23 GMT

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

Re: Creating macro variable

Reeza — Wed, 29 Jun 2016 23:57:23 GMT

I really don't expect proc sql to generate this type of error.

It might be an error in view the results in the output window. Does it work in the query?

Re: Creating macro variable

sasmom — Tue, 05 Jul 2016 16:59:02 GMT

Hi Reeza, sorry I have not replied for almost a week. I was on a break. I have not had a chance to varify the results yet. I will reply again.

Thanks