BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

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.

14 REPLIES 14
FriedEgg
SAS Employee

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;

Tom
Super User Tom
Super User

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.

Ksharp
Super User

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

Tom
Super User Tom
Super User

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.

saspert
Pyrite | Level 9

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.

art297
Opal | Level 21

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!

saspert
Pyrite | Level 9

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
Tom
Super User Tom
Super User

Change

%let nums=;

to

%let nums=0;

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

saspert
Pyrite | Level 9

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

art297
Opal | Level 21

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

Tom
Super User Tom
Super User

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.

SASJedi
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users
Ksharp
Super User

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.

Peter_C
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1731 views
  • 0 likes
  • 7 in conversation