topic Re: Delete columns if the value is empty for each field in ODS and Base Reporting

Delete columns if the value is empty for each field

Filipvdr — Thu, 03 Mar 2011 11:17:58 GMT

Is it possible to delete - drop columns if there isn't any value in for each row of the dataset?

Re: Delete columns if the value is empty for each field

data_null__ — Thu, 03 Mar 2011 13:42:29 GMT

> Is it possible to delete - drop columns if there
> isn't any value in for each row of the dataset?

Yes but you have to figure out what columns contain all missing values.

Here is one method.

[pre]
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;

data nlevels;
/*NNonMissLevels will not exist when there are no*/
/*variables with missing values*/
retain NNonMissLevels -1;
set nlevels;
run;

%let allMiss=;
proc sql noprint;
select tableVar into :allmiss separated by ' '
from Nlevels where NNonMissLevels = 0;
quit;
run;
%let allmiss = %sysfunc(IFC(&allmiss eq,%str( ),drop=&allmiss));
%put NOTE: ALLMISS=&allMiss;

proc report data=have(&allmiss) nowd;
run;
[/pre]

Re: Delete columns if the value is empty for each field

Filipvdr — Fri, 04 Mar 2011 11:01:55 GMT

ok that works quite nice! thanks a lot.

Is there something similar like: if this variable has always the same value ( you don't know the value up front)

Re: Delete columns if the value is empty for each field

data_null__ — Fri, 04 Mar 2011 13:39:22 GMT

[pre]
data have;
missing a b;
input name$ a b c d e;
*keep name a;
cards;
x 2 3 . . 7
y . 3 3 . 7
z . 3 . . 7
p . 3 . b 7
q . 3 . . 7
;;;;
run;
proc print;
run;

ods select nlevels;
ods output nlevels=nlevels;
proc freq levels;
run;
ods output close;

data nlevels;
/*NNonMissLevels will not exist when there are no*/
/*variables with missing values*/
retain NNonMissLevels 1;
set nlevels;
run;
proc print;
run;
%let oneLevel=;
proc sql noprint;
select tableVar into :oneLevel separated by ' '
from Nlevels where NLevels eq 1 and NNonMissLevels eq 1;
quit;
run;
%let oneLevel = %sysfunc(IFC(&oneLevel eq,%str( ),drop=&oneLevel));
%put NOTE: oneLevel=&oneLevel;

proc report data=have(&oneLevel) nowd;
run;
[/pre]

Re: Delete columns if the value is empty for each field

Filipvdr — Fri, 04 Mar 2011 13:55:36 GMT

ok thanks but i'm bumping into a problem now

this is what i want to have: clicking on id gives me outputdata but this data can differ according to the id

Stored Process 2 handles the id and gives the output
1 - want to get rid of columns where all missings (your first piece of code)
2 - want go place a header text where all rows have the same value (your 2nd piece of code)

when i run these codes after each other in my STP it gives the following error:

NOTE: ALLMISS=drop=PmProcedure WaferId MeasKey sWaferId sNumberOfSites aDieNumber Thick_2 Thick_3 Sum_1 Refract_1 Refract_2
201 +

202 +

203 +/* added */

204 +proc print; run;

NOTE: There were 37 observations read from the data set WORK.NLEVELS.
NOTE: The PROCEDURE PRINT printed pages 6-7.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

204 !+

205 +ods select nlevels;

206 +ods output nlevels=nlevels;

207 +proc freq levels; run;

ERROR: You cannot open WORK.NLEVELS.DATA for output access with member-level control because WORK.NLEVELS.DATA is in use by you in resource environment FREQ (223).
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.
NOTE: There were 37 observations read from the data set WORK.NLEVELS.
NOTE: The PROCEDURE FREQ printed page 8.
NOTE: PROCEDURE FREQ used (Total process time):

Re: Delete columns if the value is empty for each field

data_null__ — Fri, 04 Mar 2011 14:21:01 GMT

I don't know about stored processes. You might try ODS OUTPUT CLOSE or a different data set name NLEVELS1 and NLEVELS2.