Exporting tables with todays date in the name.

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

Steinberg
Posts: 8
Joined: Mon Jul 20, 2015 12:44 pm

Exporting tables with todays date in the name.

Postby Steinberg » Wed Nov 11, 2015 9:55 am

Dear SPSS-users.

I've been searching the internet for a while for a solution to a practical problem, and you guys are my last hope :wink:

I'm trying to export some results from the output window to excel using OMS - almost every time it's just a set of tables. I would like the name of the excel file to contain a name of the project and the date it was created. That way I would avoid overwriting my results and track my progress. It might sound stupid and I properly could just write the date every time I start working on my projects, but that wouldn't be as cool and effective as the automated way of doing it.

I know how I can be done in SAS. I would just define a macro like
%let date=today();
And then inset the macro in the export statement.

But since I'm a relative new to SPSS programming, I would really appreciate some help.

The program:

DEFINE !date (date=$time).
!ENDDEFINE.

OMS
/SELECT TABLES
/IF COMMANDS=['CTables'] SUBTYPES=['Custom Table']
/DESTINATION FORMAT=XLSX
OUTFILE =!QUOTE(!CONCAT('C:\NAME\PATH\PROJECT_output_',!date,'.XLS'))
VIEWER=YES.

GET
FILE='C:\NAME\NAME OF DATA.sav'.
DATASET NAME DataSet1 WINDOW=FRONT.

[CONTENT....]

OMSEND.

Looking forward to your help :)
JonPedersen
Posts: 118
Joined: Wed May 25, 2011 7:07 am
Contact:

Re: Exporting tables with todays date in the name.

Postby JonPedersen » Tue Nov 17, 2015 4:29 pm

Hi,
I don't think it can be done with a macro. The way to do it would be to use Python, i.e.

Code: Select all

begin program python.
import spss
import datetime
today=str(datetime.date.today())
spss.Submit("save outfile 'C:\Users\jpe\Documents\\test"+today+".sav'.")
end program.
Of course, you should replace my simple save command with your oms-command, but I was too lazy to do that. :-). Either build the command in a string or just use a spss.Submit command for each line.
(the double \\ in front of t is because it is a t - it will otherwise be interpreted as a tab)
If you need the time as well you can do today=str(datetime.datetime.now()) instead of str(today). But you will get some extra milliseconds that you may not need.
Thus, today=datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S') is probably better.
Another thing regarding your macro code: you cannot use the macro functions outside of the define - endefine block. The only thing you can use is the resulting macro.
SPSS macros are rather limited, thus Python is generally more useful. (That being said, with sufficient effort one can do quite a lot of stuff with macros also).
hth
Jon
Steinberg
Posts: 8
Joined: Mon Jul 20, 2015 12:44 pm

Re: Exporting tables with todays date in the name.

Postby Steinberg » Mon Nov 23, 2015 10:22 am

Thanks, Jon! I really appreciate it!

I'll have to look into the Python capabilities and try to remember what Python was all about - it's been awhile.

I can see, that you've exported the file to a SPSS dataset. Do you have any experience in working with Python and OMS?

What I'm trying to do is essentially to export the results in the output window to a excel file with the date in the name.

Again, I appreciate your help :D
Steinberg
Posts: 8
Joined: Mon Jul 20, 2015 12:44 pm

Re: Exporting tables with todays date in the name.

Postby Steinberg » Mon Nov 23, 2015 11:14 am

What I'm trying to do, would go something like this, so that Python sends the bit containing OMS information to SPSS:

begin program python.
import spss
import datetime
today=str(datetime.date.today())
spss.Submit("
OMS
/SELECT TABLES
/IF COMMANDS=['CTables'] SUBTYPES=['Custom Table']
/DESTINATION FORMAT=XLSX
OUTFILE ='C:\Users\...\test"+today+".XLS'.
VIEWER=YES.")
end program.

The above would then tell SPSS to start OMS and the name of my output should be "test23112015.xls"

Where I after would write the syntax for tables containing the variable of interest and then end with:

OMSEND.
JonPedersen
Posts: 118
Joined: Wed May 25, 2011 7:07 am
Contact:

Re: Exporting tables with todays date in the name.

Postby JonPedersen » Thu Nov 26, 2015 9:37 am

Hi,
Well, you are nearly there. OMS is just like any other command, so it can be used with spss.Submit,
so

Code: Select all

begin program python.
import spss
import datetime
today=str(datetime.date.today())
spsscmd="""OMS
         /SELECT TABLES
         /IF COMMANDS=['CTables'] SUBTYPES=['Custom Table']
         /DESTINATION FORMAT=XLSX
         OUTFILE='C:\\temp\\test %s.xlsx'."""%today
spss.Submit(spsscmd)
spss.Submit("ctables /table myvar by yourvar.")
spss.Submit("OMSEND.")
end program.
Note placement of ctables command.
hth
Jon
Steinberg
Posts: 8
Joined: Mon Jul 20, 2015 12:44 pm

Re: Exporting tables with todays date in the name.

Postby Steinberg » Wed Dec 02, 2015 12:58 pm

Hey Jon.

Thanks!

Now I just got to get the Python extension working.

I appriciate your help!

Who is online

Users browsing this forum: No registered users and 2 guests

cron