DATA Step, Macro, Functions and more

Managing Blank Datasets

Reply
Super Contributor
Posts: 275

Managing Blank Datasets

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.

Trusted Advisor
Posts: 1,301

Managing Blank Datasets

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;

Super User
Super User
Posts: 7,077

Managing Blank Datasets

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.

Super User
Posts: 10,046

Managing Blank Datasets

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

Super User
Super User
Posts: 7,077

Managing Blank Datasets

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.

Super Contributor
Posts: 275

Managing Blank Datasets

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.

PROC Star
Posts: 7,492

Managing Blank Datasets

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!

Super Contributor
Posts: 275

Re: Managing Blank Datasets

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).


dataset_screenshot.jpg
Super User
Super User
Posts: 7,077

Managing Blank Datasets

Change

%let nums=;

to

%let nums=0;

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

Super Contributor
Posts: 275

Re: Managing Blank Datasets

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

relationshipCMELIGCOUNTCWHAPARTcwhapcteligchapctpart
Total000           .        .           .

TR_CUR_WK_FIG dataset

title_NAME_TCWTotal
Eligible Counteligc           .
Population HA Completion Percentagehapct        .
Participants with a Completed HApart           .

Hope I am not adding more confusion. :-)

Thanks,

saspert

PROC Star
Posts: 7,492

Re: Managing Blank Datasets

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).

Super User
Super User
Posts: 7,077

Re: Managing Blank Datasets

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.

SAS Employee
Posts: 104

Managing Blank Datasets

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;
Super User
Posts: 10,046

Managing Blank Datasets

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.

Valued Guide
Posts: 2,177

Managing Blank Datasets

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

Ask a Question
Discussion stats
  • 14 replies
  • 319 views
  • 0 likes
  • 7 in conversation