topic Re: %put log file to run instead of just displaying in SAS Programming

%put log file to run instead of just displaying

imdickson — Sun, 09 Jul 2017 06:12:28 GMT

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.

Re: %put log file to run instead of just displaying

Reeza — Sun, 09 Jul 2017 07:33:54 GMT

Remove the %PUT to generate executable code.

Or write the put statements to a text file in the work library and use %include to execute the code.

You may want to look into CALL EXECUTE/DOSUBL to automate your code instead of this loop.

Re: %put log file to run instead of just displaying

Patrick — Sun, 09 Jul 2017 07:51:41 GMT

@imdickson

SAS macros are your automated typewriters. Just don't use %PUT but simpy have the SAS code generated. Once the macro stuff is done the SAS interpreter takes over and executes whatever the macro generates.

Example:

/* options 1: using macro */
options mprint;
%macro demo();

  proc sql noprint;
    select cats(libname,'.',memname) into :tbl_list separated by ' '
    from dictionary.tables
    where libname='SASHELP' and memname like 'CLASS%'
    ;
  quit;

  %if %sysfunc(countw(&tbl_list,%str( ))) = 1 %then
    %do;
      data test_singleTbl;
        set &tbl_list;
      run;
    %end;
    %do;
      data test_multipleTbl;
    %end;
        set &tbl_list;
      run;

%mend;


%demo()

I often prefer to use a data _null_ step to generate code. I then PUT the code into a temporary file and then %include this file after the run statement. I find this often easier to debug as during development is simply use a file print in the data _null_ step which shows me all the code without executing it. And once I'm happy I switch over and write to a temporary file (filename codegen temp;) and then %include codegen / source2;

Re: %put log file to run instead of just displaying

imdickson — Sun, 09 Jul 2017 09:28:33 GMT

Thanks everyone for the reply. I tried to run the code by just simply removing %put but it will prompt error.

Now my question is, by removing %put, do i have to put quotation for those strings? Words like CASE WHEN and THEN.

Re: %put log file to run instead of just displaying

imdickson — Sun, 09 Jul 2017 09:33:16 GMT

By simply removing %put and add double quotation for string such as CASE WHEN, THEN, END.

This is the error that i got :

NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

proc sql;
create table
CSTG_ACTIVITY_CONCERN_AREA as(

NOTE: Line generated by the macro variable "CWCOLUMN1".
378           ACTION_DATE
              ___________
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn2.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn3.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn4.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn5.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn6.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
11                                                         The SAS System                               09:34 Thursday, July 6, 2017

378           ,&cwcolumn7.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn8.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn9.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn10.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn11.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn12.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn13.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn14.
              _
              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn15.
              _
12                                                         The SAS System                               09:34 Thursday, July 6, 2017

              180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "K".
378           ,&cwcolumn16.

I have no clue why am i getting that as It works with %put

Re: %put log file to run instead of just displaying

Tom — Sun, 09 Jul 2017 17:40:57 GMT

The generated SQL that you copied from the log does not look like valid SQL code. For example this little block has extra characters at the end of the lines and is missing the END for the CASE statement and a name to use for the variable generated by the CASE statement.

, 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

You also have examples where you appear to be generating string literals without quotes. Like these two:

, 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

I would recommend that you post an example of the data that you want to use to generate the SQL statement and an example of a validly generated SQL statement. You can probably simplify the example to just have one or two target variables that need case statements and one or two that don't.

Since it looks like some of your CASE statements are using multiple WHEN clauses it will probably be much easier to generate your SQL statement using a data step since then you can use FIRST. and LAST. flags to know when a new target variable starts and ends.

Re: %put log file to run instead of just displaying

imdickson — Mon, 10 Jul 2017 10:13:37 GMT

Hi Reeza, by right, removing %put would make the programme works. However, I am suspecting the format/syntax in that line of statement is giving error as they are consist of string and variable all without any quotation or whatsover. Would you mind to see if there is any enhancement need to be made there or not?

Re: %put log file to run instead of just displaying

Patrick — Mon, 10 Jul 2017 10:52:52 GMT

@imdickson

"By simply removing %put and add double quotation for string such as CASE WHEN, THEN, END."

SAS Macros work purely on text level. You certainly don't need double quotes as they are treated as nothing else than just another character.

Using SAS macros can be very powerful but writing and debugging such macros needs experience and can be quite messy (and that's why some people are not big fans).

I suggest you switch over to a SAS data step and generate the code into a file which you then %include. That will make it much easier for you to "see what you get" and to test the generated code.

If you need support with this then please post the source data used to generate the SQL and also provide an example of all the SQL you'd like generated, ...and of course don't expect us to do all the work for you so post some source data for generation of one SQL for you as a starting point to build on it.

Re: %put log file to run instead of just displaying

imdickson — Mon, 10 Jul 2017 10:56:49 GMT

Hi there. The reason why the sql statement contains word like 2nd condition is because i use %put to see if it appears in LOG for testing purpose only. I have already removed it.

The generated statement should be something like this:

proc sql;
create table CSTG_WELL_TEST as (
REPORT_DT,
PLATFORM_CD,
Case When SUCTION_PRESSURE_UOM != TO then TO else TO end as SUCTION_PRESSURE_UOM,
CASE WHEN SUCTION_PRESSURE_UOM = bbs then SUCTION_PRESSURE end as SUCTION_PRESSURE,
WF_PHASE,
WF_STAGE
from CSTGT_WELL_TEST;
quit;

iT IS true that some of the case when will have multiple based on the given data from the loop. For example, some of the UOM will have more than 1 unit, so it will generate the WHEN based on the given non defaulted unit.

Come back to my original question, by simply removing %put, do i need to put any quotation or special function to wrap my statement/argument in the %IF condition? I suspect the error is due to the formatting/quatition that i need to put in after removing %put.

Re: %put log file to run instead of just displaying

Tom — Mon, 10 Jul 2017 11:13:36 GMT

So you need to make valid SAS code.

You need to add quotes around the values of string literals. Personally I would add the quotes using the QUOTE() function in the step that generats the macro variables. That will handle any embedded quotes in the data.
Make sure to include the SELECT keyword.
You should also remove the macro quoting that you added around the semicolons to make the %PUT statements function.
I would also remove the extra () that you added around the SELECT statement, but I don't think SAS really cares about that as long as you close it properly.
Not sure if your logic can detect that the first CASE statement in your example can be replaced with just a constant.

This is how I would format the example you posted.

proc sql;
create table CSTG_WELL_TEST as 
  select REPORT_DT
       , PLATFORM_CD
       , case when SUCTION_PRESSURE_UOM ^= 'TO' then 'TO'
              else 'TO' 
         end as SUCTION_PRESSURE_UOM
       , case when SUCTION_PRESSURE_UOM = 'bbs' then SUCTION_PRESSURE 
         end as SUCTION_PRESSURE
       , WF_PHASE
       , WF_STAGE
  from CSTGT_WELL_TEST
;
quit;

Re: %put log file to run instead of just displaying

imdickson — Sun, 16 Jul 2017 07:06:52 GMT

Hi everyone. Thanks for all the advices. After rounds and days of troubleshooting, i found out that the generated SQL statement(by using %put in LOG FILE tab) is not 100% correct and hence causing error.

However, after fixing the error, i am still back to the first question. How do i further process this process to let the system run my generated sql statement?

What i got is a set of codes with loops generating the necessary sql statement with conditional case when all in good shape when using %put.

I did talk to my senior and he told me what i need to do now is use datastep to assign the generated script into data step then execute the data step to run the generated sql statement. This is something new to me and i need advice on how to assign a loop into datastep and execute it.

I am kinda worried as i have already spent weeks to figure out this entire programme. It would be great if i can get an advice on how to do it.

Also, if you look at my loop, argument after %then %do seems not correct as i think we have to do assignment or logical expression within the %do right? by doing this statement:

%if xxx
%then %do;
'CASE WHEN A = 1 THEN BBB ELSE CCC;'
%end;

By looking at this sample, is it right or wrong? As there is no assignment or logical expression. Do note that it will work if i add %put to show in log file.

Re: %put log file to run instead of just displaying

Reeza — Sun, 16 Jul 2017 07:00:45 GMT

@imdickson Make a simple test case first for something this complex so you understand, in theory AND practice, what you need to build. Break it down to the simplist case, in this, how do you create macro code that executes rather than displays. Review the example from @Patrick and start with the basic code in your case and slowly expand it.

Re: %put log file to run instead of just displaying

Tom — Sun, 16 Jul 2017 13:40:56 GMT

i
Also, if you look at my loop, argument after %then %do seems not correct as i think we have to do assignment or logical expression within the %do right? by doing this statement:
%if xxx
%then %do;
'CASE WHEN A = 1 THEN BBB ELSE CCC;'
%end;
By looking at this sample, is it right or wrong? As there is no assignment or logical expression. Do note that it will work if i add %put to show in log file.

SAS macro code is used to generate SAS code that will then be executed by SAS. Your little example above will conditionally generate a string literal. Is that what you wanted? Or did you want it to conditionally generate some actual SAS code? And if you did want to generate a CASE clause for use in an SQL statement why would you generate one that is missing the END keyword and also includes an extra semi-colon?

Here is more likely what an part of a macro program that is generating a complete SQL statement that includes a CASE clause might look like.

%if (&condition) %then %do;
  case when (a=1) then bbb else ccc end
%end;

If you want an example of how to using data to generate a program take a look at the answer to this recent post. https://communities.sas.com/t5/General-SAS-Programming/Best-way-to-find-current-value-of-a-variable/m-p/375523#M48053

Basically you should start with a simple working example of code for just a few of the cases and create a program that generates that code from data for those cases. Then expand that data and make sure that your code generatation handles that larger case. One big advantage of using a data step to write the code to a file is that you can generate the code and pull it into SAS and test it to help you debug the code generation (and also debug the logic of the code that you want to generate).

Re: %put log file to run instead of just displaying

Patrick — Sun, 16 Jul 2017 13:58:04 GMT

@imdickson

The following is meant to help you. I hope you won't take offence.

You're making in my opinion two junior mistakes.

1. You start using SAS Macro language before you understand how it works and how it interacts with "normal" SAS language.

2. You've coded yourself into a mess and you are now trying desperately to fix something complex and messed-up instead of taking a step back.

What I would advise you to do:

If you're in a working environment which accepts that people can get it wrong and have to learn things before they can master them AND you've got more senior colleagues: Admit that you're seriously stuck and can't solve the problem and ask for help.

If you need to hide your situation: Take a step back! Split the big problem into multiple smaller problem and then solve one problem at a time.

The people here in this forum are very helpful and have all the knowledge required to support you BUT you need to provide all the necessary information for us to answer you with real and tested code.

I suggest you create sample data as realistic as possible and post this data here in the form of a SAS data step which creates the sample source data.

Then you explain us as exact and detailed what you need and you also provide sample output (sample code as you'd like it generated).

It could take you quite a bit of time to ask a question that strucutured but I promise you it's time well spent and you'll gain more clarity for yourself what needs to be done simply by formulating the problem in such clarity.

And last but not least:

"I did talk to my senior and he told me what i need to do now is use datastep to assign the generated script into data step then execute the data step to run the generated sql statement."

That's not really true for your case. May be your senior didn't really understand what you're doing or you didn't fully understand what you've got told. Below another code sample where SQL code gets generated conditionally by a SAS macro and then executes.

options mprint mlogic;
%macro test(selected_var);

  proc sql feedback;
    select 
      name,
      age,
      case
      %if %upcase(&selected_var) = USE_HEIGHT %then
        %do;
          WHEN height<60 THEN 'Height <60' 
          ELSE 'Height >=60'
        %end;
      %else %if %upcase(&selected_var) = USE_WEIGHT %then
        %do;
          WHEN weight<90 THEN 'Weight <90' 
          ELSE 'Weight >=90'
        %end;
          end as 
            my_selected_var length=20
    from sashelp.class
    ;
  quit;

%mend;

%test(use_weight)
%test(use_height)

Re: %put log file to run instead of just displaying

imdickson — Wed, 19 Jul 2017 03:23:34 GMT

Hi Guys. Thanks for all the advices. I truly understand about the learn from basics part. Currently i will have to settle my works on my hand first. I talked to my senior and just like what some of you all mentioned, SAS can execute the code straight after removing %put.

I tried to put double quote wrapping like :

"&comma. &&cwcolumn&k.."

(previously i never wrap it with double quote)

However, I am getting error for all the resolved variable:

May i know if there is any special handling when it is actually resolving a proc sql statement?

proc sql;
create table
CDSSTG.CSTG_ACTIVITY_CONCERN_AREA as(
select

NOTE: Line generated by the macro variable "CWCOLUMN1".
386             " ACTION_DATE
                ______________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN2".
386             ", ACTION_PARTY
                ________________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN3".
11                                                         The SAS System                               15:40 Tuesday, July 11, 2017

386             ", ACTION_REQUIRED
                ___________________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN4".
386             ", ACTIVITIES
                ______________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN5".
386             ", OFFLINE_DATE
                ________________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN6".
386             ", PLATFORM_CD
                _______________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN7".
386             ", REMARKS
                ___________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN8".
386             ", REPORT_DT
                _____________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN9".
386             ", STATUS
                __________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN10".
386             ", TARGET_DATE
                _______________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN11".
386             ", WF_COACT
                ____________
12                                                         The SAS System                               15:40 Tuesday, July 11, 2017

                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN12".
386             ", WF_DEL_DATE
                _______________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN13".
386             ", WF_PHASE
                ____________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN14".
386             ", WF_STATUS
                _____________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN15".
386             ", WF_TXNID
                ____________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN16".
386             ", WF_USERID
                _____________
                180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the macro variable "CWCOLUMN17".
386             ", WF_USERMODDATE
                __________________
                180
from CDSSTG.CSTGT_ACTIVITY_CONCERN_AREA);
ERROR 180-322: Statement is not valid or it is used out of proper order.

May i know if the double quote wrap here is wrongly used or there is something missing from it.

Re: %put log file to run instead of just displaying

Reeza — Wed, 19 Jul 2017 03:29:50 GMT

You need to post your actual code in addition to your log. We see the generated errors but not the code.

Did you do a mock example like we suggested? It will help you figure out how to build this process.

Re: %put log file to run instead of just displaying

Patrick — Wed, 19 Jul 2017 11:00:11 GMT

@imdickson

I truly understand about the learn from basics part. Currently i will have to settle my works on my hand first.

Actually, I believe you haven't understood what we're trying to tell you. If you got lost in your code then DO NOT TRY TO FIX EVERYTHING AT ONCE. Take a step back!

You've got now a macro with lots of code in it and it's not working. One way to go:

1. Create a backup of your current code.

2. Start a new code (a brand new .sas file or EG/Studio code node).

3. Copy the macro "shell" to your new code: %macro blah(...); %mend; %blah(...) - run it. If no error then proceed else debug and fix.

4. Copy the very first run group from your backed up code into the new code (ie. the first proc sql, data step etc.). If you've got already macro syntax in this first step: Remove it as far as possible - run it. If no error then proceed else debug and fix.

5. If there had been macro syntax/logic: add it back - run it. If no error then proceed else debug and fix.

6. Now add the next logical unit ....and so on and so on, step by step

For your latest question: I don't believe you need these quotes.

The way SAS Macro code interacts with "normal" SAS code:

First comes the SAS Macro guy (could also be a girl of course). This one looks all the code and searches for % and & - these are the tokens for the SAS Macro guy telling him that this is his job and he needs to do something with it. The SAS macro guy consumes everything macro and once he's done only non macro stuff remains.

Now comes the SAS girl. She picks up the "left overs" and does then does her thing with it, i.e. executes the "left over" Proc SQL syntax.

If you're using options mprint and mlogic for the code I've posted earlier then the log shows you what the macro guy does and what the SAS girl has to do.

options mprint mlogic;
/* macro definition */
%macro test(selected_var);

  proc sql feedback;
    select 
      name,
      age,
      case
      %if %upcase(&selected_var) = USE_HEIGHT %then
        %do;
          WHEN height<60 THEN 'Height <60' 
          ELSE 'Height >=60'
        %end;
      %else %if %upcase(&selected_var) = USE_WEIGHT %then
        %do;
          WHEN weight<90 THEN 'Weight <90' 
          ELSE 'Weight >=90'
        %end;
          end as 
            my_selected_var length=20
    from sashelp.class
    ;
  quit;

%mend;

/* macro call */
%test(use_weight)

The macro call generates the following log:

54         /* macro call */
55         %test(use_weight)
MLOGIC(TEST):  Beginning execution.
MLOGIC(TEST):  Parameter SELECTED_VAR has value use_weight
2                                                          The SAS System                             16:02 Wednesday, July 19, 2017

MPRINT(TEST):   proc sql feedback;
MLOGIC(TEST):  %IF condition %upcase(&selected_var) = USE_HEIGHT is FALSE
MLOGIC(TEST):  %IF condition %upcase(&selected_var) = USE_WEIGHT is TRUE
MPRINT(TEST):   select name, age, case WHEN weight<90 THEN 'Weight <90' ELSE 'Weight >=90' end as my_selected_var length=20 from 
sashelp.class ;
NOTE: Statement transforms to:

        select CLASS.Name, CLASS.Age, case 
                                        when CLASS.Weight < 90 then 'Weight <90'
                                        else 'Weight >=90'
                                        end as my_selected_var length=20
          from SASHELP.CLASS;

MPRINT(TEST):   quit;
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds
      

MLOGIC(TEST):  Ending execution.

What's shown in lines starting with MLOGIC is what the Macro guy has been doing, what's shown in lines starting with MPRINT is what the macro guy left over for the SAS girl to do; and the rest of the log shows you what the SAS girl has been doing.

And the sometimes a bit fiddely thing with macro coding: You must code carefully so that the macro guy only leaves stuff behind for the SAS girl which is really hers to deal with - this one is not very generous in this regards and throws errors if not treated with respect.

Re: %put log file to run instead of just displaying

Tom — Wed, 19 Jul 2017 15:31:07 GMT

@imdickson wrote:

Hi Guys. Thanks for all the advices. I truly understand about the learn from basics part. Currently i will have to settle my works on my hand first. I talked to my senior and just like what some of you all mentioned, SAS can execute the code straight after removing %put.

I tried to put double quote wrapping like :
"&comma. &&cwcolumn&k.."
(previously i never wrap it with double quote)

.

I seriously doubt if you want the double quotes in that situation. In SAS code you use quotes around string literals. NOT around pieces of code. From your macro variable names it looks like COMMA will have a comma in it and &&cwcolumn&k.. should resolve to a variable name. So it looks like you are trying to build part of the variable list in an SQL SELECT statement. Or perhaps part of the variable list in an ORDER BY or GROUP BY clause in an SQL SELECT statement.

So if you ran this code:

%macro gen;
%local comma k ;
%do k=1 %to &n ;
 &comma. &&cwcolumn&k..
 %let comma=,;
%end;
%mend gen;
%let n=2 ;
%let cwcolumn1=FIRST ;
%let cwcolumn2=SECOND ;
%put %gen;

The last line will cause FIRST , SECOND to be written to the SAS log.

Now if call %gen in the middle of an SQL statement then you could use it to conditionally generate a list of variable names to use in the select statement.

proc sql ;
  select %gen 
    from mydata
  ;
quit;

So it is the same as if I had just hard coded the list of variable names in the SELECT statement.

proc sql ;
  select FIRST , SECOND
    from mydata
  ;
quit;

Re: %put log file to run instead of just displaying

imdickson — Mon, 21 Aug 2017 10:20:52 GMT

Thanks everyone for the reply. Patrick's first line answered it although i spent weeks to figure out. By removing %put, what i need to do is make sure it is a proper PROC SQL format and that's it. Works like a charm. Thanks everyone.