Empty data set 1

[Queryman]

I have a program in SAS that crestes a lot of datasets, then kicks off a ddde process to export the data to Excel. This dde process fails if all the data sets do not get created, is there a way in SAS to check whther the data set got created and if it didn't create it with a line of text saying " There is no data in this time period for this".

Thanks,



Michael

 

[teralearner]

The following two system macro variables give your the last dataset created:

SYSDSN name of most recent SAS data set in two fields

SYSLAST name of most recent SAS data set in one field

If you export a sas dataset right after its creation, you could use them to check:
%if &dsn = &sysdsn %then ...

Otherwise
%SYSEXEC issues operating system commands.

You can use system commands to check your dataset.


BTW, what is ddde process?

 

[Queryman]

It's DDE, dynamic date exchange to move multiple data sets to a single excel workbooks tabs.



Michael

 

[jymm]

Micheal - which version of SAS are you running --- 8.2 had some weird qwerks about this sort of thing (I used macros & dde to switch between sheets of an excel file) but I also know that if you are on earlier versions there were some other things to watch out for.

 

[teralearner]

I was trying to find document on _infile_, and came across this piece of code that can check if a file exist:

%let filrf=myfile;
%let rc=%sysfunc(filename(filrf,
physical-filename));
%let fid=%sysfunc(fopen(&filrf));
%if &fid > 0 %then
......
%do;
%end;

 

[Queryman]

Thanks everyone, I am on ver 8.2.



Michael

 

[jymm]

sorry - been tied up...

First try using the X command (aka SYSTEM function or %sysexec macro statement).

When you are running the DDE stuff I am sure that you are doing something like
x "C:\progra~1\micros~2\Office\EXCEL.EXE" ;
run; quit;
to start up Excel

I always delete the files I am going for first (ex:
x 'del c:\SasDDETest\baseball.xls'

and then run a directory later to see what was not created by the dynamic stuff.

One of the SAS tech people (Peter) is AMAZING when it comes to DDE and macros.

Here is a sample he started me with (I changed mine a long ways to make it work, but this was a most excellent start)
options noxwait noxsync macrogen mprint symbolgen;
* deleting the newfile that we saved from previous runs of the job *;
x 'del c:\SasDDETest\baseball.xls';

filename cmds dde "excel|system";
%let drive=c;
%let rest=:\progra~1\micros~2\office\excel.exe;
%let startup=&drive&rest;
*******************************************************;
* using the string function to place the needed space *;
* to start up the excel.exe filename.xls *;
*******************************************************;
%let xlsdir=%str( c:\SasDDETest\);

*************************************************************;
* The xlsdir2 macro variable is used when the file is saved *;
* and it does not need a space *;
*************************************************************;
%let xlsdir2=c:\SasDDETest\;

* defining the original file to open *;
%let xlsfile=baseball.xls;

* defining the full string to start excel and the xls file *;
%let startup2=&startup&xlsdir&xlsfile;
%put &startup;
%put &startup2;

* defining the file that is to be saved *;
%let xlsfile1=baseball.xls;
%put &xlsfile1;
%let savefile=&xlsdir2&xlsfile1;
%put &savefile;

* %let path1=%str(%'[save.as("c:\dde\baseball3.xls&quot]%');
* now lets add the save.as command to the file we are saving *;

%let saveas=%str(%'[save.as("&savefile&quot]%');

* writes out the values of all the macro variables thus far *;
%put _user_;





data _null_;
call system(%unquote(%str(%'&startup2%')));
run; quit;

data test;
x=sleep(5);
run; quit;

* now lets run the saveas macro variable inside a macro *;
* the other comment is another way to do it *;

%macro saveit;
data _null_;
file cmds;
* put %unquote(%str(%'[save.as("&saveas&quot]%'));
put %unquote(&saveas);
run; quit;
%mend saveit;

%saveit;

---- let me know if this stuff does not help and I will see what other directions I can help with. - j

 

[Queryman]

Thanks jymm,
I got the DDE part down to a science, I open excel with this command, it works on all version by poking the registry & you do not have to worry about the path.
options noxsync noxwait xmin;
filename cmds dde 'excel|system';
data _null_;length fid rc start stop time 8;fid=fopen('cmds','s');if (fid le 0) then do;rc=system('start excel');
start=datetime();stop=start+10;do while (fid le 0);fid=fopen('cmds','s');time=datetime();if (time ge stop)
then fid=1;end;end;rc=fclose(fid);run;


The problem I have here is not the dde part but the actual SAS data set that is not getting created. I need to put out a line of code "no data for this scenario" or something like that, to create this SAS data set if it does not get created in my process.



Michael

 

[jymm]

ahhhh --- NOW I understand what you are going for. How are you creating each SAS dataset? Splitting from a larger set into a few smaller ones then doing the dde export to excel?

sorry - darn allergies have my brain in a knot this week.

 

[Queryman]

SAS datasets are created by inquiring on a table on a teradata platform.



Michael

 

[jymm]

If I understand what you are doing, you are pulling some data and seperating it into specific output excel files and the problem is that some files are empty (or they do not exist).

personally I would take the easy way out.

Create the output files first with the 'No data to report'. Those outputs that have something will overwrite.

or am I missing something?

 

[Queryman]

Okay, I will try that. Thanks



Michael

 

[bkclaw113]

Maybe a little late to help you Queryman, but maybe someone will need this.

%if %sysfunc(exist(work.port&YYMM0.)) %then %put port exists;

The exist function checks for the existance of a SAS dataset (fileexist does the same for a fileref) and returns 1 if the file exist and 0 if not.