conditional data output

[farrukh]

data test;
input cust_id rev_decile rev_data rev_sms  rev_vas;
cards;
1 10 194 132 132
2 5 157 135 161
3 9 193 118 122
4 4 139 126 193
5 3 138 160 178
6 3 135 108 176
7 1 129 166 186
8 2 129 156 102
9 2 129 160 186
10 5 159 161 141
11 1 110 148 190
12 4 141 185 180
13 9 185 175 198
14 10 199 128 158
15 6 167 108 132
16 8 175 181 132
17 8 177 159 168
18 7 169 129 104
19 6 161 181 125
20 7 174 120 123
;
run;

/*Analysis table output*/

proc sql;
 create table summary as
  select rev_decile
            ,sum(rev_data)  as tot_data_rev ,sum(rev_sms) as
tot_rev_sms , sum(rev_vas) as                      tot_vas_rev
from test
group by 1
;quit;

/*the above code will give output in scenario when all fileds have
valid data, in case any of the revenue variable is missing then i want
to output the data set which i had created below*/

/*data for error message display*/

proc sql;
   create table empty_data_message
       (Obs char(5),
        Comment char(100)
)
;QUIT;


proc sql;
insert into empty_data_message
 values('1','There are no values for this selection');
select * from empty_data_message;
quit;

I want to get the 'messaged output' if any of the field in raw file is not present. It is not necessary to follow the above procedure, looking forward for results.

Thanks in advance

[Kurt_Bremser]

First of all, creation of a 1-obs dataset is much easier achieved in a data step:

data empty_data_message;
length
  obs $5
  comment $100
;
obs = '1';
comment = 'There are no values for this selection';
run;

For giving a message, I'd do

data _null_;
set summary;
if
  tot_data_rev = . or
  tot_rev_sms = . or
  tot_vas_rev = .
then put 'There are no values for this selection';
run;

[farrukh]

Thanks for your reply, i will use this as an idea to fit my actual requirement.

[farrukh]

Actually the above solution hold true in case where the table summary is created, but in my case if the raw fields(variables e.g. any of revenue variable) is/are not present then summary table will not be created. In this scenario i want to ODS output a message 'There are no values in the selected field' and this message can be output in form of data set if we are suing ODS excel output. If there is any other way then please suggest.

[Kurt_Bremser · Accepted solution]

If you want to check if a certain variable is present in a dataset, read the table metadata from SASHELP.VCOLUMN:

%let column=XX;

data _null_;
set sashelp.vcolumn (where=(libname='SASHELP' and memname = 'CLASS')) end=done;
retain flag 1;
if upcase(name) = upcase("&column") then flag = 0;
if done and flag then put 'Errormessage';
run;