CODEWITHSUNDEEP
sqlmacrossas
Andy K
Andy K

Reputation: 5044

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)"?

Edited: Once I fetch all the variables in droplist, I'm converting it to a macrovariable with that command line %put droplist_u: &droplist_u;

%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;`

Upvotes: 1

Views: 557

Answers (2)

Laurent de Walick
Laurent de Walick

Reputation: 2174

What can create a seperate macro variable for each column you want to drop with a sequence number in it and use a loop to put them all in the keep.

%macro dropColumns;
%let pays1=IK ;
%do nopays=1 %to 1;
data _null_;
 set dico&&pays&nopays 
 (where=(intitule like 'RT1%' 
  ...
  or intitule like '%PCH_SM%')) 
 end=last;
 %do l=0 %to 5;
  count + 1;
  call symput(cats('todelete',count),catt(nomvar,"_t&&l"));
 %end;
 if last then call symput('todeleteobs',count);
run;

data a&&pays&nopays;
 data a&&pays&nopays (drop=
 %do i = 1 %to &todeleteobs.;
  &&todelete&i.
 %end;
 );
run;

%end;
%mend;

%dropColumns;

Upvotes: 0

Allan Bowe
Allan Bowe

Reputation: 12691

You could try writing your datastep (code) to a text file, then %include ing that text file - see pseudo code below (untested):

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;

Be aware that you may hit memory limits by dealing with such large datasets (depending on your operating environment obviously)

Upvotes: 2

Related Questions