Help using Base SAS procedures

alternative to a macro variable

Reply
Frequent Contributor
Posts: 75

alternative to a macro variable

I wrote the following script, see below, that is supposed to drop columns in my table that has around 70000 variables.

I've only given you a tiny part of my SQL script in the condition. Usually after all the conditions are put, the query will retrieve me

6000 variables-ish * 6.

The macro variable droplist_u is too small to keep everything. Do you have an alternative to using the macrovariable and avoid

the error "expression length (65535) exceeds maximum length (65534)"?

%let pays1=IK ;

%do nopays=1 %to 1;

%do l=0 %to 5;

 

/*one off*/

proc sql;

select catt(nomvar,"_t&&l") into : droplist_u separated by ' ' from dico&&pays&nopays

where (intitule like 'RT1%' or intitule like '%RT12%'

or intitule like 'IS-PPI%'

or intitule like 'IS-IP%'

or intitule like 'IS-IP-SA%'

or intitule like 'IS-IMPR%'

or intitule like 'IS-IMPX%'

or intitule like 'IS-IMPZ%'

or intitule like 'B-E36%'

or intitule like 'B-D_F%'

or intitule like 'B-D%'

or intitule like 'B_C%'

or intitule like 'MIG_ING%'

or intitule like 'MIG_NRG%'

or intitule like 'MIG_CAG%'

or intitule like 'MIG_COG%'

or intitule like 'MIG_DCOG%'

or intitule like '%C191%'

or intitule like '%C192%'

or intitule like '%C20_C21%'

or intitule like '%C20%'

or intitule like '%C201%'

or intitule like '%C2011%'

or intitule like '%C2012%'

or intitule like '%C2013%'

or intitule like '%C2014%'

or intitule like '%C2015%'

or intitule like '%C2016%'

or intitule like '%C2017%'

or intitule like '%C202%'

or intitule like '%C203%'

or intitule like '%C204%'

or intitule like '%C2041%'

or intitule like '%C2042%'

or intitule like '%C205%'

or intitule like '%C2051%'

or intitule like '%C2052%'

or intitule like '%C2053%'

or intitule like '%C2059%'

or intitule like '%C206%'

or intitule like '%C21%'

or intitule like '%C211%'

or intitule like '%PCH_SM%');

quit;

%put droplist_u: &droplist_u;

data a&&pays&nopays;

set a&&pays&nopays (drop=&droplist_u);

run;

%end;

%end;

%mend;

%testsql;

run;

Super User
Super User
Posts: 7,955

Re: alternative to a macro variable

Well, several ways really.

Way 1 - generate the final step:

data dropcolumns;

     set sashelp.vcolumn (where=(libname="..." and memname="..."));

     if column name in ("ISPP...","...");

run;

data _null_;

     set dropcolumns end=last;

     if _n_=1 then call execute('data WANT; set HAVE; drop=(');

     call execute(' '||strip(name));

     if last then call execute(';run;');

run;

Way two:

Normalise the data, e.g. transpose the data so you have the variables going down the table rather than across, then drop the ones you don't want and transpose the table back up again.

Super User
Super User
Posts: 7,955

Re: alternative to a macro variable

To add, you could create a dataset with your conditions and then generate from that also:

data conditions:

     attrib condition format=$200.;

     condition="intitule like 'RT1%'"; output;

     condition="intitule like '%RT12%'"; output;

...

run;

data _null_;

     set conditions end=last;

     if _n_=1 then call execute('

proc sql;

select catt(nomvar,"_t&&l") into : droplist_u separated by ' ' from dico&&pays&nopays

where ('||strip(condition)||');

     call execute(' or '||strip(condition));

     if last then call execute(');quit;');

run;

Frequent Contributor
Posts: 75

Re: alternative to a macro variable

Hi RW9,

You mean

data conditions;

RIght?

Super User
Super User
Posts: 7,955

Re: alternative to a macro variable

Sorry, where?  In my second example I create a dataset which has a list of the conditions  to be performed:

condition

intitule like 'RT1%'

...

So an example of a code generator:

data test;
  attrib teststring format=$20.;
  teststring="Hello"; output;
  teststring="World"; output;
run;
data _null_;
  set test end=last;
  if _n_=1 then do; /* This is the first row, has the text Hello */
    call execute('data newtest;
                    attrib onestring format=$200.;
                    onestring="'||strip(teststring)||'"');
  end;
  /* this code executes for all other rows */
  else do;
    call execute('||"'||strip(teststring)||'"');
  end;
  /* This code is executed at last observation */
  if last then do;
    call execute(';run;');
  end;
run;

/* Generated code from the data _null_ is */
data newtest;
  attrib onestring format=$200.;
  onestring="Hello"||"World";
run;

Frequent Contributor
Posts: 75

Re: alternative to a macro variable

RW9,

I've checked both of your solutions and they are not doing what i'm looking to do.

I'm using the proc sql because I can have a like condition to search for specific string.

Your first solution is fine but cannot do syntaxic research on strings.

The second solution , well , I still need to drop my columns , you see and ... well I guess you get it.

Best.

Super User
Posts: 5,504

Re: alternative to a macro variable

I would be careful about devising a solution that can't work anyway.  More specifically, have you talked with SAS to see whether there is a limit on the length of the SET statement?  Limits on statement size do exist in some parts of the software.

One approach would be somewhat inefficient, but would work fairly easily.  Split your macro variable into two lists instead of one.  Then apply them as separate statements:

data want;

   set have (drop=&list1);

   drop &list2;

run;

Not elegant, not fastest, but easy to get there from here.

Good luck.

Trusted Advisor
Posts: 3,214

Re: alternative to a macro variable

Not using Sql but the SAS datastep and variable lists and arrays? SAS(R) 9.4 Language Reference: Concepts, Second Edition
The SAS language has some more advantaged options as using SQL. Knowing those could be helpfull   

---->-- ja karman --<-----
Frequent Contributor
Posts: 75

Re: alternative to a macro variable

Thanks Everyone.

One acquaintance of mine gave that solution.

What do you think?

It is working fine for me.

proc sql;
create table vars as
select catt(nomvar,"_t&&l") as var
from dico&&pays&nopays
where (intitule like 'RT1%' or intitule like '%RT12%'
or intitule like 'IS-PPI%'
xxx
xxx
xxx
;quit;

filename tempf temp
;
data _null_
;
  
file tempf;
  
set vars end=lastobs;
  
if _n_=1 then put "data a&&pays&nopays;set a&&pays&nopays (drop=";
  put var
/;
  
if lastobs then put ");run;";
run
;

%inc tempf;

filename tempf clear
;

Super User
Super User
Posts: 7,955

Re: alternative to a macro variable

Yep, that's pretty much what my code is doing, however your writing the generated code out to a text file, then including that text file.  That just seems like extra work to write the fil, include it, and then of course you need to ensure it is removed.  Just change the:

if _n_=1 then put "data a&&pays&nopays;set a&&pays&nopays (drop=";
  put var
/;
  
if lastobs then put ");run;";

To:

if _n_=1 then call execute("data a&&pays&nopays;set a&&pays&nopays (drop='");
  call execute(var);

if lastobs then call execute(");run;");

Ask a Question
Discussion stats
  • 9 replies
  • 323 views
  • 6 likes
  • 4 in conversation