BookmarkSubscribeRSS Feed
andy_wk
Calcite | Level 5

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;

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

andy_wk
Calcite | Level 5

Hi RW9,

You mean

data conditions;

RIght?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

andy_wk
Calcite | Level 5

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.

Astounding
PROC Star

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.

jakarman
Barite | Level 11

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 --<-----
andy_wk
Calcite | Level 5

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
;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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
  • 9 replies
  • 1381 views
  • 6 likes
  • 4 in conversation