Generater_proc_SQL_and_REPORT

[Assaf_Attas]

קטע הקוד הבא יהיה שימושי במיוחד לעצלנים כמוני 🙂

התכנית לוקחת טבלה מסוימת ומייצר ממנה בסיס קוד ניתן לעריכה עבור פרוצדורות SQL + REPORT

%let lib 		=sashelp;
%let memname 	=class;
%let out_path 	=c:\temp;

/*---------------------------------------------------------------------------------------------------------------------------------------------*/
/* GET Dictionary */
/*---------------------------------------------------------------------------------------------------------------------------------------------*/
proc sql;
	create table vars as
	select * /*varnum, name*/
	from dictionary.columns
	where libname=UPCASE("&lib") and memname = UPCASE("&memname")
	;
quit;

proc sql noprint;
	select distinct lowcase(name)
	into :orderedvars separated by ','
	from vars
	order by varnum;
quit;

proc sql noprint;
	select distinct lowcase(name)
	into :reportvars separated by ' '
	from vars
	order by varnum;
quit;

/*---------------------------------------------------------------------------------------------------------------------------------------------*/
/* Write All Vars to log */
/*---------------------------------------------------------------------------------------------------------------------------------------------*/
%put &orderedvars;
%put ---;
%put &reportvars;

/*---------------------------------------------------------------------------------------------------------------------------------------------*/
/* CREATE Proc SQL */
/*---------------------------------------------------------------------------------------------------------------------------------------------*/
data za;
	length m_line $ 4096;
	m_line="proc sql noprint;";output;
	m_line=cats('09'x,"create table tbl_name as");output;
	m_line=cats('09'x,"select");output;
run;
data zb (keep=m_: sof);
	set vars end=sof;
	length m_line $ 4096 m_format $ 64 m_sof $ 1;
	if sof then m_sof='';else m_sof=',';
	if strip(format)='' then m_format='';else m_format=("format="||strip(format));

	m_line = cat('09'x,'09'x,strip(name)," ",repeat('09'x,3),"as ",strip(name)," ",repeat('09'x,2),strip(m_format)," ",'09'x,"label='",strip(label),"'",m_sof);
run;
data zc;
	length m_line $ 4096;
	m_line=cats('09'x,"from &lib..&memname");output;
	m_line=cats('09'x,"where 1=1");output;
	m_line=cats('09'x,";");output;
	m_line="quit;";output;
run;

data _null_ ;   
    set za zb zc ; 
    FILE  "&out_path.\proc_sql.txt"; 
    PUT  m_line ; 
run ;


/*---------------------------------------------------------------------------------------------------------------------------------------------*/
/* CREATE Proc SQL */
/*---------------------------------------------------------------------------------------------------------------------------------------------*/
data zra;
	length m_line $ 4096;
	m_line=cat("proc report data=", "&memname", " nowd split='*';");output;
	m_line=cat(' ');output;
	m_line=cats('09'x,"column &reportvars ;");output;
run;

data zrb (keep=m_: sof);
	set vars end=sof;
	length m_line $ 4096 m_format $ 64 ;
	
	if strip(format)='' then m_format='';else m_format=("format="||strip(format));

	m_line = cat('09'x,"define ",strip(name)," ",repeat('09'x,2),"/ display ",strip(m_format)," ",'09'x,"'",strip(label),"';");
run;
data zrc;
	length m_line $ 4096;
	m_line=" ";output;
	m_line="run;";output;
run;

data _null_ ;   
    set zra zrb zrc ; 
    FILE  "&out_path.\proc_report.txt"; 
    PUT  m_line ; 
run ;