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;
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.
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;
Hi RW9,
You mean
data conditions;
RIght?
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;
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.
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.
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
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;
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;");
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.