topic Re: Unmask ')' bracket from a string '%)' in a sas datastep in SAS Programming

Unmask ')' bracket from a string '%)' in a sas datastep

chithra — Thu, 12 Dec 2019 12:32:01 GMT

Dear All,

I have a program like below , which is to modify the labels of the datasets.

proc sql noprint;
select distinct quote(trim(memname)) into :mis_tables SEPARATED by ',' from sashelp.vcolumn
where libname='libref';
quit;
%put &mis_tables;

options nomlogic nosymbolgen nomprint;

/* Updating the lables */
%macro s(kname1 ,kname2,labels );
%let labels2 = %sysfunc(tranwrd(%quote(&labels),%str(??),%str(,)));

proc datasets lib=resasdmc nodetails nolist ;
modify &kname1;
label &kname2="&labels2" ;
quit;
%mend s;

data ddd(keep=table_name tech_name label_name_1);
set mis_ref_labels; //oracle table
where table_name in (&mis_tables);
label_name_1= tranwrd (label_name,',','??');
run;

data _null_;
set ddd;
call execute('%nrstr(%s('||(strip(table_name))||' , '||(strip(tech_name))||','||%str(strip(label_name_1))||'))');
run;

Table ddd:

TABLE_NAME	TECH_NAME	label_name_1
MIS_FLAT_BUS_SEG_KPI	AMT0_AAE_N	Acq Cost And Admin Exp?? Net
MIS_FACT	AMT0_AAE_O	Acq Cost And Admin Exp?? Retro
MIS_FACT	AMT1_APR_N	Agg Pol Res?? Net
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_AMOUNT	ADDCOMM_AMOUNT
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_AMOUNT_MAIN	ADDCOMM_AMOUNT_MAIN
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_DEDBASIS	ADDCOMM_DEDBASIS
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_DEDUCTION_TAB	ADDCOMM_DEDUCTION_TAB
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_FK_AMT_CY	ADDCOMM_FK_AMT_CY
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_METHOD_ID	ADDCOMM_METHOD_ID
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_METHOD_TITLE	ADDCOMM_METHOD_TITLE
MIS_BUS_COND_DEDUCTIONS	ADDCOMM_PCT	Management Expense (%)

I am gettign an error :

ERROR: Expected close parenthesis after macro function invocation not found.

Rootcause : The last row, label_name1 column is having a value Management Expense (%) where the character '%)' is masking ')'., there by causes closing parenthesis issue.

Can anyone please help here?

Thanks,

Chithra

Re: Unmask ')' bracket from a string '%)' in a sas datastep

Kurt_Bremser — Thu, 12 Dec 2019 13:12:03 GMT

Why don't you put everything into the call execute:

data ddd(keep=table_name tech_name label_name);
set mis_ref_labels; //oracle table
where table_name in (&mis_tables);
run;

data _null_;
set ddd;
call execute('
  proc datasets lib=resasdmc nodetails nolist;
  modify ' !! strip(table_name) !! ';
  label ' !! strip(tech_name) !!'="' !! strip(label_name) '";
  quit;
');
run;

Edit: removed the surplus quote, as found by @chithra

Re: Unmask ')' bracket from a string '%)' in a sas datastep

chithra — Thu, 12 Dec 2019 12:57:49 GMT

will this solves the issue?

it is throwing erro for mw..quotes issue.

Re: Unmask ')' bracket from a string '%)' in a sas datastep

chithra — Thu, 12 Dec 2019 13:01:59 GMT

it was a bracket issue in the code you provided..

corercted it now..

do you have any idea to increase the performance.

There are 4000 datasets.

Re: Unmask ')' bracket from a string '%)' in a sas datastep

Kurt_Bremser — Thu, 12 Dec 2019 13:16:20 GMT

You can modify the data step to execute only one proc datasets:

data _null_;
set ddd end=done;
by table_name;
if _n_ = 1 then call execute('
  proc datasets lib=resasdmc nodetails nolist;
');
if first.table_name then call execute('
  modify ' !! strip(table_name) !! ';
');
call execute('
  label ' !! strip(tech_name) !!'="' !! strip(label_name) '";
');
if done then call execute('quit;');
run;

Re: Unmask ')' bracket from a string '%)' in a sas datastep

chithra — Fri, 13 Dec 2019 10:29:44 GMT

Thank you.

And i am getting an error below :

ERROR: The value ' 'n is not a valid SAS name.

Re: Unmask ')' bracket from a string '%)' in a sas datastep

Kurt_Bremser — Fri, 13 Dec 2019 11:41:15 GMT

I guess you have an empty table_name or tech_name. Or some other "funny" value.

Please post the log of the code lines that cause the value; just posting the message is not helpful.

Re: Unmask ')' bracket from a string '%)' in a sas datastep

chithra — Fri, 13 Dec 2019 13:42:04 GMT

label_name =

99.5% Value At Risk (Lifeup)

This value is causing the issue..'%' character

Re: Unmask ')' bracket from a string '%)' in a sas datastep

Tom — Fri, 13 Dec 2019 14:14:16 GMT

Better to use functions to insure that the generated code has valid syntax. Use NLITERAL() to handle gooty variable names. Use QUOTE() to quote the labels using single quotes to prevent any embedded macro triggers or quote characters from causing trouble.

data ddd ;
  set mis_ref_labels(keep= table_name tech_name label_name) end=eof; 
  where table_name in (&mis_tables);
  by table_name ;
  if _n_=1 then call execute('proc datasets lib=resasdmc nolist;');
  if first.table_name then call execute(catx(' ','modify',nliteral(table_name),';'));
  call execute(catx(' ','label',nliteral(tech_name),'=',quote(trim(label_name),"'"),';'));
  if last.table_name then call execute('run;');
  if eof then call execute('quit;');
run;

Re: Unmask ')' bracket from a string '%)' in a sas datastep

chithra — Fri, 13 Dec 2019 14:19:45 GMT

Many Thanks.

It is working now !