topic Re: Export dataset within macro in SAS Programming

Export dataset within macro

iepritchard — Fri, 11 Sep 2020 18:41:02 GMT

I have a piece of code which is set to repeat with a gap of 5 minutes after each run. As part of this code after each run I want to export a SAS dataset into an open spreadsheet (which is then overwritten 5mins later as the code repeats). I want to be able to see the updated datasets as they update without stopping the loop in SAS. Any tips on how to do this please?

Re: Export dataset within macro

Kurt_Bremser — Fri, 11 Sep 2020 20:16:51 GMT

AFAIK, a spreadsheet is always write-protected if opened by another user, unless this user themselves has opened it as write-protected.

It's a similar issue in SAS: if a user has a dataset open in file viewer or any other SAS method, you can't write to it.

Re: Export dataset within macro

iepritchard — Fri, 11 Sep 2020 20:30:14 GMT

Thanks. Are there any options / settings to print a dataset to the log or output screen as the code repeats?

Re: Export dataset within macro

ballardw — Fri, 11 Sep 2020 23:28:42 GMT

What would be the advantage of specifically writing to a spreadsheet in this context?

Assuming your loops operate in the time interval you describe you would not have time to modify the results for any run. If you have more columns or rows written to than the open width by the time you try to read anything you are likely running out of time just scrolling up/down or left/right.

The only approach that I think has any chance would be 1) open the spreadsheet before anything else for DDE and then 2) use the DDE commands to update cells in the "export" section.

Conerns: DDE is not actively supported, only available for specific applications and operating systems (Microsoft applications like Excel / Windows), is very sensitive to the version of the application for some actions, not updated with promise the next "update" to Excel won't remove the links, and probably a few other things that don't come to mind as the last time I touched DDE was 5 years ago.

Re: Export dataset within macro

Kurt_Bremser — Fri, 11 Sep 2020 20:37:22 GMT

What are you actually trying to achieve with all this, what is your final goal (Maxim 43)?

Re: Export dataset within macro

iepritchard — Mon, 14 Sep 2020 12:09:04 GMT

I have a 'live' sales data table which updates in real time with sales values. Every 5 minutes I want to get the sum of all sales made and then append to a secondary table which is storing all historical records. Rather than someone clicking run every 5 mins I want to automate the process - which I think I've achieved in the code below. However, I also need to be able to see how the sales figures in the 'master' data table are increasing and this is what I don't know how to do while the macro loop is still running. Ideally I would like the 'master' data in a spreadsheet so I can have a graph/chart updating as the data updates.

%macro sales ;

%let count=0 ;

%do %until (&count=2) ;

%let count=%eval(&count+1);

/* Latest snapshot */

data interim ;

set livesales;

format runtime time. ;

runtime = time() ;

run ;

/* Append to previous records */

data master ;

set master interim ;

run ;

/* Delay next run for 5 minutes */

data _null_ ;

rc=sleep(300,1) ;

run ;

%end ;

%mend ;

%sales ;

Re: Export dataset within macro

Kurt_Bremser — Mon, 14 Sep 2020 12:58:36 GMT

Have the SAS program create a web page (ODS HTML) within the loop that has the HTTP META tag REFRESH set to 300 (use the METATEXT option in the ODS HTML statement). Then you open the page once in your browser, and it automatically refreshes every 5 minutes.

Re: Export dataset within macro

stellaa9x — Wed, 16 Sep 2020 02:16:37 GMT

I learned a lot, thanks