SAS Support Communities

Thanks! That makes sense, thanks for all the explanation. It really helps me understand this code.  For production, I have used @novinosrin's method.  I did want to try out what @FreelanceReinh suggested, so I was trying on a test window.  ... View more

Does the length of the variable have anything to do with this statement?  My variable length for "c" is $9. And it doesn't work as expected.  What am I missing?    proc sql; create table test as select c from mydata order by mod(input(c,bits24.)+1,10); quit; /* assuming ASCII encoding */ ... View more

This worked well. Thanks for the tip!  ... View more

Thanks for the tip. However, the data dump occurs automatically from a very old database. So, there is no way to remove the date stamp. We had already looked into this to save time. I posted here since that was not a possibility. I used the wildcard tip from @Reeza and it works great.  ... View more

Thanks for the wildcard idea, Reeza. I used it in my import step for all my 33 datasets and it runs perfectly.   Sharing the code for future reference   %let datafiledt=2019_0729;   %macro import (myfilename=,workfile=); proc import out=work.&workfile datafile="\\server\study\data download\&datafiledt.\&myFilename._*.csv" DBMS=CSV REPLACE; guessingrows=65465; run; %mend import;     %import (myfilename=study-datafile1,workfile=datafile1); %import (myfilename=study-datafile2,workfile=datafile2); and so on..  ... View more

That’s correct. I don’t need to keep the past data. But even I am going to, it’s alright. ... View more

@Patrick  Thank you. I was able to use this logic and apply my code to all datasets. It works great. Thanks very much for the assistance.    Posting the code here for reference: /*Dropping all date variables*/ /* define parameters */ %let libref=temp; %let col_pattern=DTC; /* use call execute to drop a list of columns dynamically populated into SAS variable DropList */ data _null_; set sashelp.vcolumn(keep=libname memname name where=(libname=%upcase("&libref") and find(name,"&col_pattern",'i')>0)); by libname memname; length DropList $32767; retain DropList; DropList=catx(',',DropList,name); if last.memname then do; call execute(catx(' ', 'proc sql; alter table', cats(libname,'.',memname),'drop', DropList, ';quit;')); call missing(DropList); end; run;     ... View more

Thanks for the tip. Could you guide me through how I would apply this step to all datasets in my directory. If I don't use call execute, what would I do here instead?    %macro drop (dn); %let dropme=; proc sql noprint; SELECT name INTO :dropMe SEPARATED BY ' ' FROM sashelp.vcolumn WHERE libname = 'TEMP' AND memname = upcase("&dn") AND INDEX(upcase(NAME),'DTC')>0; quit; %put &sqlobs; %put &dropme; %if &sqlobs > 0 %then %do; data test; set temp.&dn; drop &dropme; run; %end; %mend drop; %drop (bma); proc contents data=temp._all_ noprint out=contents; run; data _null_; set contents; by memname ; where index(upcase(name),'DTC') > 0; call execute(cats('%drop(',memname,')')); run; ... View more

The macro works fine if I try it on individual datasets. It gives me exactly what I need when I do a manual run on datasets %drop (bma); >> works great  %drop (tox); >> works great   It's only when I try the data _null_ and apply at once to all datasets, it breaks. It says no drop variables found.  ... View more

@Astounding  It's still holding to the earlier datasets DTC variables. The DTC variable name changes with each dataset.    %macro drop (dn); proc sql noprint; SELECT name INTO :dropMe SEPARATED BY ' ' FROM sashelp.vcolumn WHERE libname = 'TEMP' AND memname = upcase("&dn") AND INDEX(upcase(NAME),'DTC')>0; quit; %if &sqlobs > 0 %then %do; data test; set temp.&dn; drop &dropme; run; %end; %mend drop; proc contents data=temp._all_ noprint out=contents; run; data _null_; set contents; by memname ; if first.memname; where index(upcase(name),'DTC') > 0; call execute(cats('%drop(',memname,')')); run; ... View more

That was only an example of what I am trying to achieve and not the actual macro.    I tried this way, but it still holds on to the pre-resolved macro.     %macro drop (&dn); proc sql noprint; SELECT name INTO :dropMe SEPARATED BY ' ' FROM sashelp.vcolumn WHERE libname = 'TEMP' AND memname = upcase("&dn") AND INDEX(upcase(NAME,'DTC'))>0; quit; %if &sqlobs > 0 %then %do; data test; set temp.&dn; drop &dropme; run; %end; %mend drop; proc contents data=temp._all_ noprint out=contents; run;   data _null_; set contents; by memname ; if first.memname; call execute(cats('%drop(',memname,')')); run; ... View more