DATA Step, Macro, Functions and more

%put log file to run instead of just displaying

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

%put log file to run instead of just displaying

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.


Accepted Solutions
Solution
‎08-21-2017 06:19 AM
Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to imdickson

@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;

View solution in original post


All Replies
Super User
Posts: 19,858

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

Posted in reply to imdickson

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. 

 

Frequent Contributor
Posts: 77

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

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?
Solution
‎08-21-2017 06:19 AM
Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to imdickson

@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;

Frequent Contributor
Posts: 77

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

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.
Frequent Contributor
Posts: 77

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

Posted in reply to imdickson

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

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to imdickson

@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.

Frequent Contributor
Posts: 77

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

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.
Super User
Super User
Posts: 7,074

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

[ Edited ]
Posted in reply to imdickson

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.

 

 

Frequent Contributor
Posts: 77

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

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.

 

 

 

 

 

Super User
Super User
Posts: 7,074

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

Posted in reply to imdickson

So you need to make valid SAS code.

 

  1. 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.
  2. Make sure to include the SELECT keyword.
  3. You should also remove the macro quoting that you added around the semicolons to make the %PUT statements function.
  4. 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.
  5. 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;
Frequent Contributor
Posts: 77

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

[ Edited ]

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.

 

Super User
Posts: 19,858

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

Posted in reply to imdickson

@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. 

 

Super User
Super User
Posts: 7,074

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

Posted in reply to imdickson

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/...

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).

 

 

 

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to imdickson

@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)

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 403 views
  • 2 likes
  • 4 in conversation