|
There are a number of way to work around the macro variable length.
One way would be more macro variables but I like %inc better.
Don't know why you program doesn't work. This one does, however there
is also a limit to statement length (method 1). So you might need to
code gen complete statements (method2).
data have;
missing a b;
input name$ a b c d e;
*keep name a;
cards;
x 2 . . . .
y . . 3 . .
z . a . . .
p . . . b .
q . . . . .
;;;;
run;
proc print;
run;
proc format; *To speedup PROC FREQ;
value allmiss ._-.z=. other=1;
value $allmiss ' '=' ' other='1';
run;
ods select nlevels;
ods output nlevels=nlevels;
proc freq levels;
format _character_ $allmiss. _numeric_ allmiss.;
run;
ods output close;
filename FT46F001 temp;
Method1: data _null_;
file ft46f001;
/*NNonMissLevels will not exist when there are no*/
/*variables with missing values*/
retain NNonMissLevels -1;
put 'set have(drop=';
do until(eof);
set nlevels end=eof;
if NNonMissLevels eq 0 then put tableVar;
end;
put ');';
stop;
run;
data need;
%inc FT46F001 / source2;
run;
Method2: data _null_;
file ft46f001;
/*NNonMissLevels will not exist when there are no*/
/*variables with missing values*/
retain NNonMissLevels -1;
set nlevels end=eof;
if NNonMissLevels eq 0 then put +3 'drop ' tableVar ';';
run;
data need;
set have;
%inc FT46F001 / source2;
run;
On 4/12/10, William Krause <wkrause2003@yahoo.com> wrote:
> I have a dataset that contains 5000 rows but contains around 20,000
> variables. The variable name are too large to fit into a macro variable
> (exceeds the max length).
>
> How can I find all the variables which contain only missing values (blanks
> for character variables) and (. for numeric variables).
>
> I have some code from somewhere that uses a macro variable but it only
> works within the macro length limits.
>
> This is the code I used -- doesn't work for large variable lists.
>
> All I'm concerned with is identifying the variables that contain values
> that are all blank.
>
> **** Finding and Dropping Emtpy Variables that contain all blank/missing
> values. ******* ;
> options symbolgen;
>
> data class;
> set sashelp.class;
> name=' ';
> ndummy=.;
> cdummy=' ';
>
> proc sql noprint;
> select 'case when nmiss('||compress(name)||')=count(*) then "'
> ||compress(name)||'" else " " end' into :nlst separated by "||' '||"
> from dictionary.columns
> where libname='WORK' and memname='CLASS'
> ;
> select distinct &nlst
> into :droplst
> from class
> ;
>
> data class;
> set class;
> drop &droplst;
> run;
>
> proc print;
> run;
>
> I had modified the program above to write out the cases statements to a
> external file. I modified it but I couldn't get it to work either.
> I put a statements in the external dataset:
> proc sql ;
> create table blnkmrv as
>
> Also removed the comma from the end of the last case statement.
>
> libname blnk " C:\MYDATA" ;
> proc sql noprint;
> CREATE TABLE BLNK_CASES AS
> select 'case when nmiss('||compress(name)||')=count(*) then "'
> ||compress(name)||'" else " " end' || ','
> from dictionary.columns
> where libname='BLNK' and memname='MYCASES'
> ;
> quit ;
>
> FILENAME TMP "C:\TEMP\BLNKDATA.SAS" ;
> DATA _NULL_ ;
> SET BLNK_CASES ;
> FILE TMP ;
> PUT _TEMA001 ;
> RUN ;
>
>
> PROC SQL ;
> %INCLUDE TMP
> ;
> QUIT ;
>
>
> You can ignore the code above.
>
> All I need I a solution that will process the 20000 variables.
>
> Thanks for you assistance.
>
> Bill K.
> wkrause2003@yahoo.com
>
| 
