Hi Everyone. I have developed a simple programme where it will do a loop based on the source table and read all its column name with special handling for CASE WHEN.
Here is the thing. I managed to complete the programme for all the data that supposed to be display out IN LOG FILE ONLY, meaning I am only using %put for all the results that I want.
Since I have already managed to get what i want, i want to get those data to be executed by programme instead of just displaying in log file.
The output that i generate using loop is actually a proc sql statement with different table name and column name each time it loops.
Let me show some sample data:
proc sql;
create table
CSTG_WELLMON_SURFACE_PRESSURE as(
case when CCP_UOM = PSIG then CCP_UOM 2nd condition
when CCP_UOM = BARG then CCP * formula 3rd condition
when CCP_UOM = kPa then CCP * formula 3rd condition
, case when CCP_UOM ne PSIG then PSIG else PSIG end
, case when DHP_UOM = PSIG then DHP_UOM 2nd condition
when DHP_UOM = BARG then DHP * formula 3rd condition
when DHP_UOM = kPa then DHP * formula 3rd condition
, case when DHP_UOM ne PSIG then PSIG else PSIG end
, case when DHT_UOM = °C then DHT_UOM 2nd condition
when DHT_UOM = °F then DHT * formula 3rd condition
, case when DHT_UOM ne °C then °C else °C end
, case when FLP_UOM = PSIG then FLP_UOM 2nd condition
when FLP_UOM = BARG then FLP * formula 3rd condition
when FLP_UOM = kPa then FLP * formula 3rd condition
, case when FLP_UOM ne PSIG then PSIG else PSIG end
, case when FLT_UOM = °C then FLT_UOM 2nd condition
when FLT_UOM = °F then FLT * formula 3rd condition
, case when FLT_UOM ne °C then °C else °C end
, case when FTHP_UOM = PSIG then FTHP_UOM 2nd condition
when FTHP_UOM = BARG then FTHP * formula 3rd condition
when FTHP_UOM = kPa then FTHP * formula 3rd condition
, case when FTHP_UOM ne PSIG then PSIG else PSIG end
when GAS_LIFT_INJECT_RATE_UOM = MMscf/d then GAS_LIFT_INJECT_RATE * formula 3rd condition
when GAS_LIFT_INJECT_RATE_UOM = km3/d then GAS_LIFT_INJECT_RATE * formula 3rd condition
when GAS_LIFT_INJECT_RATE_UOM = skL/d then GAS_LIFT_INJECT_RATE * formula 3rd condition
when GAS_LIFT_INJECT_RATE_UOM = scf/d then GAS_LIFT_INJECT_RATE * formula 3rd condition
when GAS_LIFT_INJECT_RATE_UOM = bbtu/d then GAS_LIFT_INJECT_RATE * formula 3rd condition
,GAS_LIFT_INJECT_RATE_UOM_
, case when ICP_UOM = PSIG then ICP_UOM 2nd condition
when ICP_UOM = BARG then ICP * formula 3rd condition
when ICP_UOM = kPa then ICP * formula 3rd condition
, case when ICP_UOM ne PSIG then PSIG else PSIG end
,OFFLINE_DATE
, case when PCP_UOM = PSIG then PCP_UOM 2nd condition
when PCP_UOM = BARG then PCP * formula 3rd condition
when PCP_UOM = kPa then PCP * formula 3rd condition
, case when PCP_UOM ne PSIG then PSIG else PSIG end
,PLATFORM_CD
,REMARKS
,REPORT_DT
, case when SCP_UOM = PSIG then SCP_UOM 2nd condition
when SCP_UOM = BARG then SCP * formula 3rd condition
when SCP_UOM = kPa then SCP * formula 3rd condition
, case when SCP_UOM ne PSIG then PSIG else PSIG end
, case when SITHP_UOM = PSIG then SITHP_UOM 2nd condition
when SITHP_UOM = BARG then SITHP * formula 3rd condition
when SITHP_UOM = kPa then SITHP * formula 3rd condition
, case when SITHP_UOM ne PSIG then PSIG else PSIG end
,SURFACE_PRESSURE_DATE
,WELL_NAME
,WELL_STATUS
,WELL_TYPE
,WELL_UPTIME
,WF_COACT
,WF_DEL_DATE
,WF_PHASE
,WF_STATUS
,WF_TXNID
,WF_USERID
,WF_USERMODDATE
, case when WHT_UOM = °C then WHT_UOM 2nd condition
when WHT_UOM = °F then WHT * formula 3rd condition
, case when WHT_UOM ne °C then °C else °C end
) from CSTGT_WELLMON_SURFACE_PRESSURE;
I copied the above proc sql statement from log file. The log files has 37 proc sql with each different source table.
Now, I want the programme to run based on the above sql statement so that when the source data can do unit conversion when the source data does not match with the default unit.
Here are some of my partial code(please refer to "inner loop* where i do all the %put to see my output:
%macro conversionmacro;
/*get distinct tablename which starts with CSTGT_*/
proc sql noprint;
create table sourcetblname as(
select distinct memname
from dictionarytable
where memname like 'CSTGT\_%' escape '\');
quit;
/*get distinct count record which is 37*/
proc sql noprint;
select count(*) into:tabledistinct
from sourcetblname;
quit;
/*Assign distinct all table name into tblnm macro array variable, 37 rows*/
/*proc sql noprint;*/
/*select distinct memname into:tblnm1-:tblnm&SysMaxLong*/
/*from dictionarytable;*/
/*Join UOM Mapping and UOM standard*/
PROC SQL noprint;
create table uomjoined as (
SELECT T1.SOURCETABLE,
SUBSTR(CAT(T1.SOURCETABLE),7,19) AS SHORTSOURCETABLE,
T1.SOURCEVALUENAME,
T1.SOURCEUOMNAME,
T1.SOURCEUOMGROUP,
T1.TARGETTABLE,
T1.TARGETVALUENAME,
T1.TARGETUOMNAME,
T1.TARGETUOMGROUP,
T2.UOM_GROUP,
T2.FROM,
T2.TO,
T2.FORMULA,
CASE WHEN T2.FROM=T2.TO THEN '1' else '2' END AS DEFAULTFLAG
FROM CDSEXT.CEXT_EXF_UOM_MAPPING T1
LEFT JOIN CDSEXT.CEXT_EXF_STANDARD_UOM T2 ON T1.SOURCEUOMGROUP=T2.UOM_GROUP
/*WHERE T1.SOURCETABLE LIKE '%EQUIP%'*/
);
QUIT;
/*group by to extract from uomjoined*/
proc sql noprint;
create table uomjoinedforuom as(
select distinct sourcetable,sourceuomname, to
from uomjoined);
/*group by sourcetable, sourceuomname,to;*/
quit;
/*proc sql;*/
/*select t1.memname , */
/*t1.name,*/
/*t2.sourceuomname,*/
/*t2.sourceuomgroup,*/
/*t2.from,*/
/*t2.to,*/
/*t2.formula */
/*from dictionarytable t1 left join uomjoined t2*/
/*on t1.memname=t2.sourcetable and t1.name=t2.sourcevaluename;*/
/*quit;*/
/*left join to get full metadata table with uom details*/
proc sql noprint;
create table METAFULLWITHUOM as(
select t1.tablename ,
t1.columnname,
t2.sourceuomname,
t2.sourceuomgroup,
/*newly added 29th june*/
t2.targetuomname,
t2.targetuomgroup,
t2.from,
T2.to,/*CASE WHEN SUBSTR(t2.sourceuomgroup,1,LENGTH(t2.sourceuomgroup)-4)=t2.sourceuomname THEN t2.TARGETUOMGROUP ELSE T2.TO END,*/
t2.formula ,
case when t2.from=t2.to and t2.from is not null then 1
when t2.from is null then 0
else 2 end as DEFAULTFLAG
from sourcetablev2 t1 left join uomjoined t2
on SUBSTR(t1.tablename,1,19)=SUBSTR(t2.sourcetable,1,19) and t1.columnname=t2.sourcevaluename)
ORDER BY t1.tablename, t1.columnname,t2.sourceuomname,T2.DEFAULTFLAG ;
quit;
proc sql noprint;
create table METAFULLWITHUOM2 AS(
select t1.tablename ,
t1.columnname,
t1.sourceuomname,
t1.sourceuomgroup,
t1.targetuomname,
t1.targetuomgroup,
t1.from,
T1.to,
t1.formula,
t2.to as uomto,
T1.DEFAULTFLAG
from METAFULLWITHUOM t1 left join uomjoinedforuom t2 on SUBSTR(t1.tablename,1,19)=SUBSTR(t2.sourcetable,1,19) and TRIM(t1.columnname)=TRIM(t2.sourceuomname))
order by tablename, columnname, DEFAULTFLAG ASC;
quit;
proc sql noprint;
select distinct tablename into:tblnm1-:tblnm&SysMaxLong
from metafullwithuom;
quit;
PROC SQL noprint;
SELECT MEMNAME, NAME FROM DICTIONARYTABLE where MEMname like '%CSTGT%';
QUIT;
/*get source tablename and column name that starts with CSTGT*/
PROC SQL noprint;
CREATE TABLE SOURCETABLEV2 AS(
select memname as tablename,
name as columnname
from dictionarytable
where tablename like '%CSTGT%');
quit;
/*add target tablename and column name that starts with CSTG*/
PROC SQL noprint;
CREATE TABLE TARGETTABLEV2 AS(
select SUBSTR(memname,1,4) || SUBSTR(memname,6) as tablename,
name as columnname
from dictionarytable
where memname like '%CSTGT%');
QUIT;
/*PROC SQL noprint;*/
/*create table joinsrctgt as(*/
/*select a.tablename, a.columnname,b.tablename*/
/*from SOURCETABLEV2 a INNER JOIN TARGETTABLEV2 b*/
/*on substr(SOURCETABLEV2.tablename,7)=substr(TARGETTABLEV2.tablename,6) and a.columnname=b.columnname);*/
/*quit;*/
/*proc sql noprint;*/
/*select distinct tablename into:sourcetblnm1-:sourcetblnm&SysMaxLong*/
/*from SOURCETABLEV2;*/
proc sql noprint;
select distinct tablename into:sourcetblnm1-:sourcetblnm&SysMaxLong
from SOURCETABLEV2;
quit;
proc sql noprint;
select distinct tablename into:targettblnm1-:targettblnm&SysMaxLong
from TARGETTABLEV2;
quit;
/*outer loop*/
%do i=1 %to &tabledistinct.;
%let tblnm = &&tblnm&i..;
%let sourcetblnm = &&sourcetblnm&i..;
%let targettblnm = &&targettblnm&i..;
/*%put &tblnm;*/
/*start of inner loop*/
/*create table to stores all column name belongs to CSTGT*/
proc sql noprint;
select count(*) into:columncountnumber
from dictionarytable
where memname = "&tblnm.";
quit;
/*%put &columncountnumber;*/
/*assign columns into array list*/
proc sql noprint;
select COLUMNNAME into:columnarray1-:columnarray&SysMaxLong
from SOURCETABLEV2
where TABLENAME = "&tblnm.";
quit
;
proc sql noprint;
CREATE TABLE ONLYCOLUMNWITHUOM AS(
select t2.tablename ,
t2.columnname,
t2.sourceuomname,
t2.sourceuomgroup,
t2.from,
t2.to,
t2.formula
/*row_number() over(order by t2.columnname) RowNumber*/
from metafullwithuom t2
where sourceuomname is not null);
quit;
/*try logic*/
/*proc sql;*/
/*select columnname into: casewhencolumnarray1-:casewhencolumnarray&SysMaxLong*/
/*from ONLYCOLUMNWITHUOM;*/
/*quit;*/
/**/
/*proc sql;*/
/*select sourceuomname into: casewhensourceuomarray1-:casewhensourceuomarray&SysMaxLong*/
/*from ONLYCOLUMNWITHUOM;*/
/*quit;*/
proc sql noprint;
select columnname, sourceuomname, sourceuomgroup,targetuomname, targetuomgroup,from, to, formula , uomto, DEFAULTFLAG
into: cwcolumn1-:cwcolumn&SysMaxLong,:cwsourceuomname1-:cwsourceuomname&SysMaxLong,
:cwsourceuomgroup1-:cwsourceuomgroup&SysMaxLong,:targetuomname1-:targetuomname&SysMaxLong,:targetuomgroup1-:targetuomgroup&SysMaxlong, :cwfrom1-:cwfrom&SysMaxLong, :cwto1-:cwto&SysMaxLong,
:cwformula1-:cwformula&SysMaxlong, :cwuomto1-:cwuomto&SysMaxLong, :cwdefaultflag1-:cwdefaultflag&SysMaxLong
from METAFULLWITHUOM2
where TABLENAME = "&tblnm.";
quit;
proc sql noprint;
select count(*) into:cwcount
from METAFULLWITHUOM
where TABLENAME = "&tblnm."
/*newly added 29th june 2017*/
;
quit;
/*TRY WITH UOM ONLY COLUMN*/
proc sql noprint;
select columnname, sourceuomname, sourceuomgroup,from, to, formula
into: cwv2column1-:cwv2column&SysMaxLong,:cwv2sourceuomname1-:cwv2sourceuomname&SysMaxLong,
:cwv2sourceuomgroup1-:cwv2sourceuomgroup&SysMaxLong,:cwv2from1-:cwv2from&SysMaxLong, :cwv2to1-:cwv2to&SysMaxLong, :cwv2formula1-:cwv2formula&SysMaxlong
from ONLYCOLUMNWITHUOM
where TABLENAME = "&tblnm.";
quit;
/*%let sqlheader = proc sql; create table &targettblnm as(;*/
/*%put &sqlheader;*/
/*print table name*/
%put proc sql%STR(;);
%put create table;
%put &targettblnm as(;
%let comma =;
%let null=;
%let case2=case;
%let comma2=,;
%let one=1;
%let two=2;
%let previous=;
/*proc sql noprint; */
/*create table &targettblnm as(*/
%do k=1 %to &cwcount;
/*first condition is to change unit only*/
%if "&previous" ne "&&cwcolumn&k.." %then %do;
%let tamat=end;
%end;
%else %do;
%let tamat=;
%end;
%if "&&cwuomto&k.." ne "&null" %then %do;
/* %put &tamat. */
%put &comma. case when &&cwcolumn&k.. ne &&cwuomto&k.. then &&cwuomto&k..
else &&cwuomto&k.. end;
%end;
/*second condition is to convert based on formula*/
%else %if "&&cwsourceuomname&k.." ne "&null" and "&&cwdefaultflag&k.." eq "&one" %then %do;
/* %put */
%put &comma. case when &&cwsourceuomname&k.. = &&cwto&k.. then &&cwsourceuomname&k.. 2nd condition;
%end;
%else %if "&&cwsourceuomname&k.." ne "&null" and "&&cwdefaultflag&k.." eq "&two" %then %do;
%put
when &&cwsourceuomname&k.. = &&cwfrom&k.. then &&cwcolumn&k * &&cwformula&k.. formula 3rd condition ;
%end;
%else %do;
%put
&comma&&cwcolumn&k..;
%end;
%let comma=,;
%let previous=&&cwcolumn&k..;
%end;
%put ) from &sourcetblnm%STR(;);
quit;
%let sqlfooter = ) from &sourcetblnm%STR(;);
/* %put ) from &sourcetblnm%STR(;);*/
/*%put &sqlfooter;*/
%end;
%mend;
%conversionmacro;
I need your advice to change my log file proc sql statement back to the programme itself instead of %put.
Click image to register for webinar
