python & SQL & macro
Locked
 
|
SPSS20
We run our database queries with macros. Since we want to run queries every day (for the past day), we would need a dynamic timestamp within the macro. Therefore we tried to combine macro with python by using the command spss.SetMacroValue. But this does not work. What is wrong - or is there any other solution instead of python/macro? (solutions in SQL slow down the query) Code: begin program. import datetime, string, spss when = datetime.datetime.today() first = datetime.date(day=1, month=when.month, year=when.year) firstThisMonth=first.strftime('%Y-%m-%d %H:%M:%S') prev_month_end = first - datetime.timedelta(days=1) prev_month_start = datetime.date(day=1, month= prev_month_end.month, year= prev_month_end.year) firstDay=prev_month_start.strftime('%Y-%m-%d %H:%M:%S') spss.SetMacroValue("!timestamp1", "'" + firstDay+ "'") spss.SetMacroValue("!timestamp2", "'" +firstThisMonth+ "'") end program. DEFINE macdef1 (arg_macdef1 = !TOKENS(1) / arg_macdef2 = !TOKENS(1) / arg_macdef3 = !TOKENS(1)). GET DATA /TYPE=ODBC /CONNECT= !arg_macdef1 /SQL= (...) deliveryDate >= !timestamp1 and deliveryDate < !timestamp2;' (...) /ASSUMEDSTRWIDTH=255. CACHE. EXECUTE. DATASET NAME !arg_macdef2 WINDOW=FRONT. SAVE OUTFILE=!arg_macdef3. !ENDDEFINE. macdef1 arg_macdef1 = 'DSN=www1;' arg_macdef2 = www1 arg_macdef3 = 'E:\www1.sav'.
Dr. Frank Gaeth
|
|
|
spss.SetMacroValue works fine, so any issues
you have should be the same as if the macro had been defined directly using
DEFINE. SET MPRINT ON will show you what code is actually being run
after the macro expansion.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] phone: 720-342-5621 From: drfg2008 <[hidden email]> To: [hidden email], Date: 04/03/2014 08:06 AM Subject: [SPSSX-L] python & SQL & macro Sent by: "SPSSX(r) Discussion" <[hidden email]> SPSS20 We run our database queries with macros. Since we want to run queries every day (for the past day), we would need a dynamic timestamp within the macro. Therefore we tried to combine macro with python by using the command spss.SetMacroValue. But this does not work. What is wrong - or is there any other solution instead of python/macro? (solutions in SQL slow down the query) Code: begin program. import datetime, string, spss when = datetime.datetime.today() first = datetime.date(day=1, month=when.month, year=when.year) firstThisMonth=first.strftime('%Y-%m-%d %H:%M:%S') prev_month_end = first - datetime.timedelta(days=1) prev_month_start = datetime.date(day=1, month= prev_month_end.month, year= prev_month_end.year) firstDay=prev_month_start.strftime('%Y-%m-%d %H:%M:%S') spss.SetMacroValue("!timestamp1", "'" + firstDay+ "'") spss.SetMacroValue("!timestamp2", "'" +firstThisMonth+ "'") end program. DEFINE macdef1 (arg_macdef1 = !TOKENS(1) / arg_macdef2 = !TOKENS(1) / arg_macdef3 = !TOKENS(1)). GET DATA /TYPE=ODBC /CONNECT= !arg_macdef1 /SQL= (...) *deliveryDate >= !timestamp1 and deliveryDate < !timestamp2;'* (...) /ASSUMEDSTRWIDTH=255. CACHE. EXECUTE. DATASET NAME !arg_macdef2 WINDOW=FRONT. SAVE OUTFILE=!arg_macdef3. !ENDDEFINE. macdef1 arg_macdef1 = 'DSN=www1;' arg_macdef2 = www1 arg_macdef3 = 'E:\www1.sav'. ----- Dr. Frank Gaeth FU-Berlin -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/python-SQL-macro-tp5725226.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
thank you Jon, good news that it works ! The problem then is solvable.
The error message: >A '+' was found following a text string, indicating continuation, but the next >non-blank character was not a quotation mark or an apostrophe We tried different versions: '+!timestamp1+' +!timestamp1+ !timestamp1 nothing works (could not find a proper working example on the internet) or maybe the error occurs already while defining: spss.SetMacroValue("!timestamp1", "'" + firstDay+ "'") how many apostrophes, quotation marks have to be set (also could not find a proper working example)
Dr. Frank Gaeth
|
Administrator
|
Frank,
I have DELETED my two attempts to help which would have solved your problem. Ignore my advice. FINE! DELETE! Happy hunting "nothing works "? Far from it! --
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
Thanks David for your advice:
DEFINE test (). PRESERVE. SET MPRINT ON. ECHO !timestamp1. ECHO '!timestamp1'. ECHO !QUOTE(!timestamp1). ECHO !EVAL(!timestamp1). ECHO !EVAL('!timestamp1'). ECHO !QUOTE(!EVAL(!timestamp1)). RESTORE. !ENDDEFINE. test. we found the reason: double quotes " instead of single quotes ' are needed. !QUOTE(!EVAL(!timestamp1)) produces '2014-03-24' but we would need: "2014-03-24" Thanks again for your help.
Dr. Frank Gaeth
|
|
|
The problem is still unsolved. We tried lots of combinations but we did not get what we need.
We would need this: "2014-03-24" (date with quotation marks). What we got so far is: 2014-03-24 !timestamp1 !timestamp1 2014-03-24 2014-03-24 '2014-03-24' "!timestamp1" "'2014-03-24'" with the following commands: DEFINE test (). PRESERVE. SET MPRINT ON. ECHO !timestamp1. ECHO '!timestamp1'. ECHO !QUOTE(!timestamp1). ECHO !EVAL(!timestamp1). ECHO !EVAL('!timestamp1'). ECHO !QUOTE(!EVAL(!timestamp1)). ECHO !QUOTE(!CONCAT('"',!timestamp1,'"')). ECHO !QUOTE(!CONCAT('"',!EVAL(!timestamp1),'"')). RESTORE. !ENDDEFINE. test. ************** full program ********************* SET MPRINT ON. begin program. import datetime,spss today = datetime.date.today() mondayOfLastWeekObject = today- datetime.timedelta(days=today.weekday(),weeks=1) mondayOfLastWeek = (today- datetime.timedelta(days=today.weekday(),weeks=1)).strftime('%Y-%m-%d') sundayOfLastWeek = (mondayOfLastWeekObject+datetime.timedelta(days=6)).strftime('%Y-%m-%d') print mondayOfLastWeek print sundayOfLastWeek spss.SetMacroValue("!timestamp1", "'" + mondayOfLastWeek+ "'") spss.SetMacroValue("!timestamp2", "'" + sundayOfLastWeek+ "'") end program. DEFINE test (). PRESERVE. SET MPRINT ON. ECHO !timestamp1. ECHO '!timestamp1'. ECHO !QUOTE(!timestamp1). ECHO !EVAL(!timestamp1). ECHO !EVAL('!timestamp1'). ECHO !QUOTE(!EVAL(!timestamp1)). ECHO !QUOTE(!CONCAT('"',!timestamp1,'"')). ECHO !QUOTE(!CONCAT('"',!EVAL(!timestamp1),'"')). RESTORE. !ENDDEFINE. test.
Dr. Frank Gaeth
|
|
|
Does this work?
'select * from tblBlah where date = "' + !quote(!eval(!timestamp1)) + '";' Regards, Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ----- Original Message ----- > From: drfg2008 <[hidden email]> > To: [hidden email] > Cc: > Sent: Friday, April 4, 2014 12:00 PM > Subject: Re: [SPSSX-L] python & SQL & macro > >T he problem is still unsolved. We tried lots of combinations but we did not > get what we need. > > We would need this: "2014-03-24" (date with quotation marks). > > What we got so far is: > > 2014-03-24 > !timestamp1 > !timestamp1 > 2014-03-24 > 2014-03-24 > '2014-03-24' > "!timestamp1" > "'2014-03-24'" > > with the following commands: > > > DEFINE test (). > PRESERVE. > SET MPRINT ON. > ECHO !timestamp1. > ECHO '!timestamp1'. > ECHO !QUOTE(!timestamp1). > ECHO !EVAL(!timestamp1). > ECHO !EVAL('!timestamp1'). > ECHO !QUOTE(!EVAL(!timestamp1)). > ECHO !QUOTE(!CONCAT('"',!timestamp1,'"')). > ECHO !QUOTE(!CONCAT('"',!EVAL(!timestamp1),'"')). > RESTORE. > !ENDDEFINE. > test. > > > ************** full program ********************* > > > SET MPRINT ON. > > begin program. > import datetime,spss > > today = datetime.date.today() > mondayOfLastWeekObject = today- > datetime.timedelta(days=today.weekday(),weeks=1) > mondayOfLastWeek = (today- > datetime.timedelta(days=today.weekday(),weeks=1)).strftime('%Y-%m-%d') > sundayOfLastWeek = > (mondayOfLastWeekObject+datetime.timedelta(days=6)).strftime('%Y-%m-%d') > > print mondayOfLastWeek > print sundayOfLastWeek > > spss.SetMacroValue("!timestamp1", "'" + > mondayOfLastWeek+ "'") > spss.SetMacroValue("!timestamp2", "'" + > sundayOfLastWeek+ "'") > end program. > > > DEFINE test (). > PRESERVE. > SET MPRINT ON. > ECHO !timestamp1. > ECHO '!timestamp1'. > ECHO !QUOTE(!timestamp1). > ECHO !EVAL(!timestamp1). > ECHO !EVAL('!timestamp1'). > ECHO !QUOTE(!EVAL(!timestamp1)). > ECHO !QUOTE(!CONCAT('"',!timestamp1,'"')). > ECHO !QUOTE(!CONCAT('"',!EVAL(!timestamp1),'"')). > RESTORE. > !ENDDEFINE. > test. > > > > > > ----- > Dr. Frank Gaeth > FU-Berlin > > -- > View this message in context: > http://spssx-discussion.1045642.n5.nabble.com/python-SQL-macro-tp5725226p5725271.html > > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
Administrator
|
In reply to this post by drfg2008
Note that you should be looking at what appears on the ECHO statement, not what is echoed!
Might be a simpler way, but this is what I diddled together before becoming fed up and disgusted. DEFINE test (). PRESERVE. SET MPRINT ON. ECHO !CONCAT('"',!UNQUOTE(!EVAL(!timestamp1)),'"'). RESTORE. !ENDDEFINE. test. > ECHO "2014-03-01 00:00:00". 2014-03-01 00:00:00
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
In reply to this post by Albert-Jan Roskam
nope, this (+ !quote(!eval(!timestamp1)) +) works only within Python. But I have the problem to combine Macro with Python.
@ David: > ECHO "2014-03-01 00:00:00". 2014-03-01 00:00:00 It is exactly the opposite way round: "2014-03-01 00:00:00" should be the result. I tried it with !CONCAT(), but that adds extra apostrophe. ECHO !QUOTE(!CONCAT('"',!EVAL(!timestamp1),'"')). leads to: " '2014-03-24' " although ECHO !EVAL('!timestamp1'). alone produces no apostrophe (2014-03-24) but it adds them as soon as it is in combination with !CONCAT().
Dr. Frank Gaeth
|
|
Administrator
|
WAKE UP FRANK!
!CONCAT('"',!UNQUOTE(!EVAL(!timestamp1)),'"') BUILDS THE DOUBLE QUOTED STRING!!!!!!!!!!!!!!!!!!!!!!! "2014-03-01 00:00:00" !UNQUOTE you silly Kraut!!!!!!!!!
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
Thank you mondayOfLastWeek=r'"%s"'%str(mondayOfLastWeek1) sundayOfLastWeek=r'"%s"'%str(sundayOfLastWeek1) would do as well. However, doesn't work within a macro. So we better run it completely within Python. -> During the American Civil War, the physician John Jay Terrell (1829–1922)[22][23] was able to successfully reduce the death rate from disease among prisoners of war; he attributed this to the practice of feeding his patients raw sauerkraut.[24](Wikipedia)
Dr. Frank Gaeth
|
|
Administrator
|
Frank,
Did the zombies eat your brains?
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
In reply to this post by drfg2008
At 10:05 AM 4/3/2014, drfg2008 wrote:
>We run our database queries with macros. Since we want to run >queries every day (for the past day), we would need a dynamic >timestamp within the macro. Therefore we tried to combine macro with >python by using the command spss.SetMacroValue. But this does not >work. What is wrong - or is there any other solution instead of python/macro? As you're well aware, there's no pure-macro solution possible, because macros have no access to dynamic information, time or any other. That leaves you, basically, with Python or native SPSS to generate time stamps. Some years back I wanted to time-stamp SPSS output, and wrote a workable, if lengthy, program in native SPSS, which I append. When I used it, I invoked it by an INCLUDE statement at the head of the program to be time-stamped, but it would wrap nicely in a macro -- in a macro, you wouldn't even need "SET PRINTBACK = NO". (If you need the timestamp as a macro, rather than just printed in the listing, you could use code similar to this to write the macro definition to a scratch file and then INSERT the result.) Here it is: PRESERVE. SET PRINTBACK=NO. NEW FILE. INPUT PROGRAM. STRING NOW (A22). /* Present date/time, */ /* "mm/dd/yyyy hh:mm:ss AM" */ /* Scratch intermediate variables */ NUMERIC #RGTNOW (DATETIME22) /#TIME (TIME10) /#TIME12 (TIME10). STRING #MDY (A10) /#HMS (A08) /#AMPM (A02). COMPUTE #RGTNOW = $TIME. COMPUTE #TIME = XDATE.TIME(#RGTNOW). DO IF (#TIME < TIME.HMS(01)). /* Midnight -< 1:00 AM */ . COMPUTE #TIME12= #TIME + TIME.HMS(12). . COMPUTE #AMPM = 'AM'. ELSE IF (#TIME < TIME.HMS(12)). /* 1:00 AM -< Noon */ . COMPUTE #TIME12= #TIME. . COMPUTE #AMPM = 'AM'. ELSE IF (#TIME < TIME.HMS(13)). /* Noon -< 1:00 PM */ . COMPUTE #TIME12= #TIME. . COMPUTE #AMPM = 'PM'. ELSE. /* 1:00PM -< Midnight */ . COMPUTE #TIME12= #TIME - TIME.HMS(12). . COMPUTE #AMPM = 'PM'. END IF. COMPUTE #MDY = STRING(#RGTNOW,ADATE10). COMPUTE #HMS = STRING(#TIME12,TIME08). COMPUTE NOW = CONCAT(#MDY ,' ',#HMS,' ',#AMPM). PRINT / ' (TimeStmp.INC 16 Nov 2005) Run at ' NOW. END CASE. END FILE. END INPUT PROGRAM. EXECUTE. /* Change notes are HERE, so they won't be printed back. */ /* --------------------------------------------------------------- */ /* 10 Nov 2003 - Create, de novo */ /* 12 Nov 2003 - Start change notes, following final EXECUTE, so */ /* they don't break up the code but aren't printed back. */ /* (PRINTBACK=NO takes effect only with the first *statement* */ /* after it is issued.) */ /* Bells and whistles: Print time in AM/PM form, using code I */ /* wrote for that for an SPSSX-L posting. */ /* 25 Feb 2004 - Add an EXECUTE statement at the end. It seems to */ /* help when INCLUDEd from a file that is itself INCLUDEd. */ /* (See SPSSX-L thread "INCLUDE vs. direct: sub-INCLUDEd files",*/ /* Mon, 23 Feb 2004 22:55:24 -- Tue, 24 Feb 2004 14:18:52) */ /* 09 Nov 2004 - Change vrsn.date in output line from "25 Nov 2004"*/ /* to "25 Feb 2004". I don't know how THAT one got by! */ /* 16 Nov 2005 - Shorten all lines to <=72 characters, so lines */ /* don't wrap it it's posted to the E-list. */ /* It's mostly these change notes that need changing. */ /* Add "NEW FILE." before "INPUT PROGRAM."; INPUT PROGRAM isn't */ /* accepted if there's an active file. (So, this erases any.) */ RESTORE. EXECUTE. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
In reply to this post by drfg2008
Just to provide a proper working solution in python instead of using a macro:
(in the example 3 dbs are queried one by one with the time range of last week) begin program. import datetime,spss today = datetime.date.today() mondayOfLastWeekObject = today- datetime.timedelta(days=today.weekday(),weeks=1) mondayOfLastWeek = (today- datetime.timedelta(days=today.weekday(),weeks=1)).strftime('%Y-%m-%d %H:%M:%S') sundayOfLastWeek = (mondayOfLastWeekObject+datetime.timedelta(days=7)).strftime('%Y-%m-%d %H:%M:%S') #sundayOfLastWeek = (mondayOfLastWeekObject+datetime.timedelta(days=7)).strftime('%Y-%m-%d 23:59:59') #print mondayOfLastWeek #print sundayOfLastWeek start = r'%s'%('"'+mondayOfLastWeek+'"') end = r'%s'%('"'+sundayOfLastWeek+'"') print start print end dsnConn= ['db1','db2','db3'] for everyConn in dsnConn: spss.Submit(r""" GET DATA /TYPE=ODBC /CONNECT='DSN="""+everyConn+""";' /SQL='SELECT ... ' where aggregationType=0 AND (deliveryDate>= """+start+""" and '+ 'deliveryDate< """+end+""" ) ... /ASSUMEDSTRWIDTH=255. CACHE. EXECUTE. DATASET NAME """+everyConn+""" WINDOW=FRONT. ... SAVE OUTFILE='C:\\...\\...\\filename"""+everyConn+""".sav'. """) END PROGRAM.
Dr. Frank Gaeth
|
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |