topic Managing Blank Datasets in SAS Programming

Managing Blank Datasets

saspert — Wed, 12 Oct 2011 22:45:46 GMT

Hi,

I have a scenario where my proc sqls generate 0 records. But there are several steps that are dependant on this resultant datasets like transpose and sql and print. Is there a way to manage blank datasets in a away that there are no errors thrown because of missing fields?

Thanks,

saspert.

Managing Blank Datasets

FriedEgg — Wed, 12 Oct 2011 23:11:25 GMT

dkricond and dkrocond come to mind, however I am unable to remember the specifics circumstances they aid in off hand.

proc options group=sasfiles; run;

proc options group=errorhandling; run;

Managing Blank Datasets

Tom — Wed, 12 Oct 2011 23:15:58 GMT

If you are using a macro then you can test the automatic macro variable SQLOBS that will tell you how many observations were generated.

proc sql noprint ;

create table x as select * from y where id = &id ;

quit;

%if &sqlobs %then %do;

... other steps ...

%end;

Otherwise I think we would need to know the details of the other steps to suggest solutions.

Managing Blank Datasets

Ksharp — Thu, 13 Oct 2011 06:30:32 GMT

If you use &sqlobs with noprint option. the value of &sqlobs is not right.

You need to remove noprint option if you want to use automatic macro &sqlobs.

Ksharp

Managing Blank Datasets

Tom — Thu, 13 Oct 2011 13:33:28 GMT

Actually that is not true. It could be true if you were using a select statement without either an INTO or a CREATE, but that is not a real world situation where you would want to count the observations.

Managing Blank Datasets

saspert — Fri, 14 Oct 2011 20:05:14 GMT

Hi guys,

Here is my macro code -

original
%MACRO _CONVERT_MV_TO_ZERO(DATASET);
PROC STDIZE DATA=&DATASET OUT=&DATASET REPONLY MISSING=0;
VAR _NUMERIC_;
RUN;
%MEND _CONVERT_MV_TO_ZERO;

modified

%MACRO _CONVERT_MV_TO_ZERO(DATASET);
%let nums=;
proc sql noprint;
    select count(*) into :nums
      from dictionary.columns
         where libname="WORK"
            and memname=upcase("&DATASET.")
    and type="num"
     ;
quit;

%if &nums. gt 0 %then %do;
    PROC STDIZE DATA=&DATASET OUT=&DATASET REPONLY MISSING=0;
     VAR _NUMERIC_;
    RUN;
%end;
%MEND _CONVERT_MV_TO_ZERO;

I think Prod stdize throws an error "No variables to analyze" whenever it encounters a blank dataset -which I dont want to happen. Now after adding the proc sql to count the # of columns, I dont get back the correct results. When I run a proc sql on dictionary.columns, I see all the relevant fields and other metadata. But when I run it on the actual dataset, I get back 0 as the result (&num variable) which is not correct.

Any suggestions would be appreciated.

Thanks,
saspert.

Managing Blank Datasets

art297 — Fri, 14 Oct 2011 20:14:20 GMT

I'm confused about what you are trying to do. Are you trying to find out if you have any observations or are you trying to find out if you have any variables?

Your code is looking for the latter!

Managing Blank Datasets

Tom — Fri, 14 Oct 2011 20:19:29 GMT

Change

%let nums=;

%let nums=0;

When there are no records selected INTO will not change the value of the target macro variable.

Re: Managing Blank Datasets

saspert — Fri, 14 Oct 2011 20:55:48 GMT

looking for # of numeric columns. now that you ask i wonder if it makes sense to check the obs or the columns.

Now I know - I need to check for the numeric columns because there maybe three records in the dataset but no numeric columns for proc stdize to work on (see my screenshot).

Re: Managing Blank Datasets

saspert — Fri, 14 Oct 2011 20:57:07 GMT

oh really? i never knew that. let me check again.

You may be right there but I think my dataset is dropping the numeric variables at the proc transpose step itself. so the macro is running fine but when I get to proc report then it gives me an error.

/****CURRENT WEEK******/
proc sql;
reset noprint;
create table cw_totals as
select "Total" as Relationship,
sum(cmeligcount) as cmeligcount,
sum(cwhapart) as cwhapart
from cur_wk;
quit;
data cur_wk_fig;
length relationship $5;
set cur_wk cw_totals;
cwhapct=divide(cwhapart,cmeligcount);
eligc = put(cmeligcount,comma12.);
hapct = put(cwhapct,percent12.1);
part= put(cwhapart, comma12.);
run;
%_CONVERT_MV_TO_ZERO(cur_wk_fig)
proc transpose data=cur_wk_fig out=tr_cur_wk_fig
prefix=TCW
;
ID relationship;
VAR eligc hapct part;
run;
/* run the same code for the current week data */
/* merge in the same information for the current week by relationship */

data tr_cur_wk_fig;
length title $60 ;
set tr_cur_wk_fig;
select (_name_);
when ("eligc") title="Eligible Count";
when ("hapct") title="Population HA Completion Percentage";
when ("part") title="Participants with a Completed HA";
otherwise;
end;
run;
proc sort data=tr_cur_wk_fig;
by _name_;
run;

%_CONVERT_MV_TO_ZERO(work.tr_cur_wk_fig)

data overview;
merge tr_cuml_fig(in=a) tr_cur_wk_fig(in=b);
by _name_;
if a and b then output;
run;

%_CONVERT_MV_TO_ZERO(work.overview)

/* produce the proc report */
proc report data=overview nowd
style(header)=[just=center font_face=arial foreground=white bordercolor=black background=#003300 font_weight=bold];
columns title ("Cummulative" TCMLtotal TCML0 TCML1) ("Current Week" TCWtotal TCW0 TCW1);
define title / display "Health Assessment Completion Overview" right;
define TCMLtotal / display "Eligible Population" right;
define TCML0 / display "Employees" right;
define TCML1 / display "Spouses/Domestic Partners" right;
define TCWtotal / display "Eligible Population" right;
define TCW0 / display "Employees" right;
define TCW1 / display "Spouses/Domestic Partners" right;
run;
quit;

CUR_WK_FIG dataset

relationship	CMELIGCOUNT	CWHAPART	cwhapct	eligc	hapct	part
Total	0	0	0	.	.	.

TR_CUR_WK_FIG dataset

title	_NAME_	TCWTotal
Eligible Count	eligc	.
Population HA Completion Percentage	hapct	.
Participants with a Completed HA	part	.

Hope I am not adding more confusion. 🙂

Thanks,

saspert

Re: Managing Blank Datasets

art297 — Fri, 14 Oct 2011 22:12:35 GMT

Sorry about my last naive question. Yes, what you are doing makes sense and I agree with Tom's suggestoin.

In answer to your current question, no. proc transpose will still result with numeric variables .. just more of them (i.e., times the number of ids).

Re: Managing Blank Datasets

Tom — Fri, 14 Oct 2011 23:43:22 GMT

It is a little hard to figure out the purpose of the different steps.

But it seems to me that should move the call to zero out the numeric variables BEFORE the step where you are converting them to character variables.

%_CONVERT_MV_TO_ZERO(cur_wk_fig)

data cur_wk_fig;

length relationship $5;

set cur_wk cw_totals;

cwhapct=divide(cwhapart,cmeligcount);

eligc = put(cmeligcount,comma12.);

hapct = put(cwhapct,percent12.1);

part= put(cwhapart, comma12.);

run;

Or maybe even before take the sums.

Managing Blank Datasets

SASJedi — Sun, 16 Oct 2011 19:29:02 GMT

A simpler (and slightly more efficient) way to find the number of numeric variables in a table is to query DICTIONARY.TABLES for the num_numeric column. This has the added benefit in that, if there are 0 numeric columns, the query actually returns a 0 value. Something like this would probably do the trick:

 
proc sql;
select num_numeric
   into :nums
   from DICTIONARY.TABLES
   where libname="WORK"
      and memname="%QUPCASE(&DATASET)")
;
quit;

Managing Blank Datasets

Ksharp — Tue, 18 Oct 2011 05:24:46 GMT

Yes. I tested it. Using create table... can also return the right sqlobs I never think about it.

But Actually you do not need noprint any more ,since you create table that will defaultly give no print.

Managing Blank Datasets

Peter_C — Wed, 19 Oct 2011 13:57:49 GMT

ksharp

the noprint is important if you want to load into :macroVariable because that cannot work with create (unless you are happy to have a report of the select query results in the output)

peterC