topic Re: Pick Only SQL part from SAS code in SAS Programming

Pick Only SQL part from SAS code

arunrami — Thu, 19 Dec 2019 14:15:05 GMT

I am doing some mass code update task, for that will have to find only the proc sql step from all the sas code in certain path ,

it has been very boring and time taking process of manually going in to all the code ans search "proc sql" ..

though I am using sas code to find all the code with "proc sql" keyword, it is picking only that particular line , not the whole sql script under "proc sql".

Just wondering , is there any simplest way I can take proc sql step from sas code, either by using file handling program or sas EG ?

-SAR

Re: Pick Only SQL part from SAS code

Oligolas — Thu, 19 Dec 2019 14:27:23 GMT

Yes, one way do that would be using regular expressions.

It could be tricky if you have constructs that dynamically generate SQL code on run time. You might have to check the results by hand.

Could you upload a test file for me to work with please?

Are you in a windows environment?

Re: Pick Only SQL part from SAS code

novinosrin — Thu, 19 Dec 2019 14:25:08 GMT

"though I am using sas code to find all the code with "proc sql" keyword," - Sounds like you wrote a very smart and a great code. I wish I could write like that. Can you please share the code if you don't mind. I would like to use that too.

@arunrami wrote:

I am doing some mass code update task, for that will have to find only the proc sql step from all the sas code in certain path ,

it has been very boring and time taking process of manually going in to all the code ans search "proc sql" ..

though I am using sas code to find all the code with "proc sql" keyword, it is picking only that particular line , not the whole sql script under "proc sql".

Just wondering , is there any simplest way I can take proc sql step from sas code, either by using file handling program or sas EG ?

-SAR

Re: Pick Only SQL part from SAS code

gamotte — Thu, 19 Dec 2019 14:30:38 GMT

Hello,

With vim, you can open a fiile in the given path and issue the command :

:vim "proc sql" *.sas

It will search for the string "proc sql" in all sas files in the same directory.

You can access the list of results with the command

:cw

It opens a new buffer with the list of results (file names, line numbers, code extract). If you move the cursor on

a given line and hit enter, it will open the corresponding file at the appropriate line.

You can use "<CTRL-w> w" to switch between results buffer and sas code.

Re: Pick Only SQL part from SAS code

Astounding — Thu, 19 Dec 2019 14:50:29 GMT

Just as you are using a program to locate all instances of "proc sql" you could use the same program to locate "quit;" which marks the end of the SQL step.

There are complications. Some programmers don't bother to add the QUIT statement. Sometimes macro language generates part or all of the SQL code, so you don't actually see "proc sql" in the program itself.

Sometimes, SQL code and macro code get intertwined. For example:

a SELECT statement extracts data into a macro variable.
a %LET statement utilizes the value of that macro variable.
another SELECT statement executes

All of this can happen within a single PROC SQL. So you have to decide if you want to extract the macro code along with the SQL statements.

The bottom line: search for "quit;" can help shorten the task, but probably won't get you 100% of the results that you need.

Re: Pick Only SQL part from SAS code

arunrami — Thu, 19 Dec 2019 16:53:56 GMT

Inspired by@Shmuel post - https://communities.sas.com/t5/SAS-Programming/Searching-SAS-code-for-keywords/m-p/390379/highlight/true#M93625

Re: Pick Only SQL part from SAS code

ErikLund_Jensen — Thu, 19 Dec 2019 17:40:06 GMT

Hi @arunrami

As others pointed out, there can be many problems, but the following code will de the rough work. It extracts all sql steps beginning with a line starting with proc sql and ending with a new line starting with quit , or until a new line starts with proc, data or run.

Arguments to the macro is

the name of a data set with a list of all programs (full path), and
the name of the variable containing the path.

%macro scanfiles(filelist,filevar);
	proc datasets lib=work nolist nodetails nowarn;
		delete jobsfound;
	quit;

	%let pgmcnt = 0;
	proc sql noprint;
		select count(*) into :pgmcnt
		from &filelist;
	quit;
	%put &=pgmcnt;

	%do i = 1 %to 200; *&pgmcnt;
		data _null_;
			set &filelist(obs=&i firstObs=&i);
			call symput("filsti",&filevar);
		run;

		filename saspgm "&filsti";
		data d (drop=instep sqlfound startrec);
			length filsti $255;
			infile SASpgm truncover lrecl = 255 end=eop;
			retain StepNo 0 instep 0 sqlfound 0 startrec;
			Filsti = "&filsti";

			input @1 s $char255.;
			s = tranwrd(s,'09'x,'    ');
			recno = _N_;

			if lowcase(s) =: 'proc sql' then do;
				startrec = recno;
				sqlfound = 1;
				instep = 1;
				StepNo = StepNo + 1;
			end;
			if recno > startrec and lowcase(scan(s,1,'; ')) in ('proc','data','run') then instep = 0;
			if instep = 1 and s ne '' then output;
			if lowcase(scan(s,1,'; ')) = 'quit' then instep = 0;
			if eop then call symputx('sqlfound',sqlfound);
		run;
		filename saspgm clear;
		%put &=filsti &=sqlfound;

		%if &sqlfound > 0 %then %do;
			proc append base=jobsfound data=d; 
			run;
		%end;
	%end;
%mend;
%scanfiles(jobliste2,filsti);

Re: Pick Only SQL part from SAS code

arunrami — Thu, 19 Dec 2019 19:50:17 GMT

Thanks @ErikLund_Jensen .

Re: Pick Only SQL part from SAS code

PGStats — Fri, 20 Dec 2019 04:11:17 GMT

In Notepad++ you can search and mark with regular expression:

proc sql\b.+?\bquit;

to highlight every SQL code block in SAS code. Check option ". matches newline" in search dialog.

Re: Pick Only SQL part from SAS code

arunrami — Thu, 26 Dec 2019 09:36:02 GMT

Hi @ErikLund_Jensen , just now gone through the code , it is really inspiring and of course it works like a charm

I don't understand why you are using tranwrd here ..can you please elaborate bit more

input @1 s $char255.;
   s = tranwrd(s,'09'x,'    ');
   recno = _N_;s = tranwrd(s,'09'x,'    ');

Thanks you again!

Re: Pick Only SQL part from SAS code

ErikLund_Jensen — Mon, 30 Dec 2019 11:52:27 GMT

Hi @arunrami

The tranwrd function is not necessary. It changes a tab char ('09'x) to 4 spaces, and the sole purpose is to preserve indentation, so the code snippets look nice in the SAS data viewer.

The programs I used to test the code are written using SAS Diaplay manager, where indentation of lines is made with tab characters, and each tab is shown as 4 spaces in the program editor. But the tab character has no meaning in the SAS data viewer, they simply diapppear, so all lines in a proc sql statement block are left aligned. I wanted the code snippets to be shown the same way as when the program is opened in Display Manger, so I translated the tabs to 4 spaces.

Re: Pick Only SQL part from SAS code

arunrami — Mon, 30 Dec 2019 12:50:38 GMT

Got it!! . Thanks !!