Export generated code with proc sql

[Aude]

Hi,

I'm seeking for help to export the autogenerated code by an other way that using the button because SAS crash with error message without processing the query.

My job consist in modify very long and complex queries, with many connections. I want to export all the code on unique file to make an easy's search and replace.

I would like a proc sql for this use.

I've found this code for BCP prompt command line, but it doesn't work on SAS EG and I don't know how and if I can use it.

SELECT SQL.definition + CHAR(10) + CHAR(13) + 'GO' AS CodeModule
INTO ##MODULES
FROM sys.objects AS OBJETS
JOIN sys.sql_modules AS SQL ON SQL.object_id = OBJETS.object_id
WHERE OBJETS.type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
AND OBJETS.name NOT LIKE 'dt%' AND OBJETS.name NOT LIKE 'sp%'
ORDER BY OBJETS.object_id
 
DECLARE @szBCP VARCHAR(256);
SET @szBCP = 'bcp "SELECT CodeModule FROM ##MODULES" queryout C:\ModulesSQL.sql -T -c';
EXEC master.dbo.xp_cmdshell @szBCP;
 
DROP TABLE ##MODULES;

Thanks for any help.

Have a nice day.

[Tom]

What SQL dialect is that code written for?  

What does the code do?

If you want to run it in SAS you would need to adapt it to use either PROC SQL or PROC FEDSQL syntax.

[Aude]

This SQL code is for SQL server.

When I adapt it with PROC SQL syntax, the BCP's part ("DECLARE @szBCP VARCHAR(256)...") isn't recognize by the program.

PROC SQL; 
SELECT SQL.definition + CHAR(10) + CHAR(13) + 'GO' AS CodeModule
INTO ##MODULES
FROM sys.objects AS OBJETS
JOIN sys.sql_modules AS SQL ON SQL.object_id = OBJETS.object_id
WHERE OBJETS.type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
AND OBJETS.name NOT LIKE 'dt%' AND OBJETS.name NOT LIKE 'sp%'
ORDER BY OBJETS.object_id
 
DECLARE @szBCP VARCHAR(256);
SET @szBCP = 'bcp "SELECT CodeModule FROM ##MODULES" queryout C:\ModulesSQL.sql -T -c';
EXEC master.dbo.xp_cmdshell @szBCP;
 
DROP TABLE ##MODULES;

QUIT;

Have you an other method to export all the code in a unique file ? 

[Tom]

Just pasting SQL Server code into PROC SQL will not change into a syntax that PROC SQL understands.  You would need to convert the SQL Server syntax into syntax that SAS understands.

If you want to run SQL Server code you need to connect to an SQL Server database and send the code to run there.

proc sql;
  connect to .... as SQLS;

But you usually use that to either pull DATA from the remote database. 

select * from connection to sqls 
  (select * from myschema.mytable)
;

Or possibly run some simple steps in the remote 

execute by sqls
  (update myschema.mytable set status='DONE')
;

What is it that you are trying to do?  What does this mean?

Have you an other method to export all the code in a unique file ? 

What code? From where?  Do you have code stored in a table in your SQL server database?

Do you have SAS code in files somewhere? If so why are you trying to use SQL to read files?  Just use a normal SAS data step to read text files.

[Aude]

Indeed, maybe you're right. I don't know if a proc SQL is better than a SAS data step, here. 

I'm a beginner on SAS, I've started the learning with my entreprise on march but because of the COVID-19 my learning stopped but not my job 😁. And the show must go on, without knowledges 😅.

To resume, I would like "translate" the button "Export All Code In Project" on the picture, to a program code. Because when I use the button, SAS encounter an error. I think there is a time out problem because of query's complexity.

[Tom]

That is not something you would do from SAS code. So you need someone that really uses Enterprise Guide to answer that.  You might even ask SAS support to help you.

Does the code work? If so then why do you need to export? Why not just keep running it with Enterprise Guide?

Or are you only trying to export the generated code because it does work?  If so what step in the process flow doesn't work?

[Aude]

Yes, the code work.

My company's agencies are located throughout France, and some agencies develop queries that they share with me. But I need to adapt queries to my territory's standards. For example, the main modification is the ID of my district, and this modification can be realized several times in the same query. So I need to regroup all the code in a unique program in order to facilitate the modification.

[TomKari]

An Enterprise Guide project is just a .zip file. So if you copy the project to another file, and rename the extension to .zip, you can treat it as any other zipped file. It can be a little complex, but the code will be in there.

Tom

[Aude]

Thanks Tom, this solution allows me to control modifications in each query. It's a good trick ^^ but it's not exactly what I'm looking for. I would like to decrease the number of modifying step and don't have to open each query to modifying it.

An exemple of common query in my work :

[AlanC]

Tom is on the right track, IMO, to your solution. Based upon what I have seen, here is how I would approach your problem. You can decide if this is your path (modify the tech/approach to uit you).

1. Put zip file in an accessible location

2. Write a C#/Python program to read the XML. These should be self-describing in their XML engines (they are in C#) meaning you don't need a map.

3. Search the nodes for the pattern needed using XPath.

4. Modify the XML in place.

5. Close XML. Close zip..

In all honesty, what you are describing is not complex to achieve. If you want to pull out the SQL where found, again, extract the node and its children to a string and place in a file. 

I use C# for XML vs SAS since I don't have to create a map. XSLT would also work in this scenario if you want to go that route.

XPath is your friend here. You don't need to extract it and do a search and replace. XML handles that for you using XSLT/XPath. The nodes in your EG project are simply well-constructed XML in the egp file.

[TomKari]

Here's a little piece of code that I jiffied up a few years ago. You might find it to be useful.

Tom

/* Put your EG project and the directory pointing to it in these two variables */
%let ProcessDir = C:\ddd;
%let DSN = project.egp;

/* Get code from a zip file member */
%macro GetZipCode(ProcessDir, DSN, ZIPMember, SASMember); 
filename inzip zip "&ProcessDir.\&DSN." member="&ZIPMember.";
data _null_;
infile inzip;
file "&ProcessDir.\&SASMember.";
input;
put _infile_;
run;
%mend; /* GetZipCode */

/* Assign a fileref wth the ZIP method */
filename inzip zip "&ProcessDir.\&DSN.";
 
/* Read the "members" (files) from the ZIP file */
data _null_;
 length memname $200;
 fid=dopen("inzip");
 if fid=0 then
  abort;
 memcount=dnum(fid);
 do i=1 to memcount;
  memname=dread(fid,i);
  if scan(memname, 2, "/") = "code.sas"
  then call execute("%"||"GetZipCode(&ProcessDir., &DSN., "||trim(left(memname))||", "||trim(left(scan(memname, 1, "/")))||".sas);");
 end;
 rc=dclose(fid);
run;