topic Re: Delete the empty space in XML file in SAS Programming

Delete the empty space in XML file

Babloo — Thu, 22 Mar 2018 08:18:35 GMT

SAS dataset to XML converion

Babloo — Mon, 19 Mar 2018 16:55:02 GMT

I have the placed below the snippet of SAS program which will convert the SAS dataset to XML file. However when I do, the field dpf_base is not filled in in the SAS dataset but the field <dynamicPricingFactor> in the xml file is filled in with a ‘.’ instead of leaving it as empty. Appreciate if someone of you guide me to resolve this issue.

put '<dynamicPricingFactorPolicyCover>';
	put   '<productVersionCoverRef>';
	put '    <externalIdentifier>'  cover +(-1) '</externalIdentifier>';
	put '   </productVersionCoverRef>';
	put '    <isForNonMatch>false</isForNonMatch>';
	put '    <hashCode1>'  veh_driver_hashcode +(-1)  '</hashCode1>';
	put '    <hashCode2>' version_subversion_hashcode +(-1) '</hashCode2>';
	put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>';
	put '    <dynamicPricingFactorPolicyCoverLangList>';
	put '       <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>FR</externalIdentifier>';
	put '            </languageRef>';
	a=translate(expl_base,'-','/');
	put '            <explanation>' a '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '        <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>VLS</externalIdentifier>';
	put '            </languageRef>';
	put '            <explanation>' a '</explanation>';

	*put '            <explanation>' translate(expl_base,'-','/') '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '    </dynamicPricingFactorPolicyCoverLangList>';
	put ' </dynamicPricingFactorPolicyCover>';

Re: SAS dataset to XML converion

Reeza — Mon, 19 Mar 2018 17:28:55 GMT

Set option missing=‘’;

or add an IF statement to conditionally put the value or missing

or use COALESCE to determine the actual value to handle the missing value.

@Babloo wrote:

put '<dynamicPricingFactorPolicyCover>';
	put   '<productVersionCoverRef>';
	put '    <externalIdentifier>'  cover +(-1) '</externalIdentifier>';
	put '   </productVersionCoverRef>';
	put '    <isForNonMatch>false</isForNonMatch>';
	put '    <hashCode1>'  veh_driver_hashcode +(-1)  '</hashCode1>';
	put '    <hashCode2>' version_subversion_hashcode +(-1) '</hashCode2>';
	put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>';
	put '    <dynamicPricingFactorPolicyCoverLangList>';
	put '       <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>FR</externalIdentifier>';
	put '            </languageRef>';
	a=translate(expl_base,'-','/');
	put '            <explanation>' a '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '        <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>VLS</externalIdentifier>';
	put '            </languageRef>';
	put '            <explanation>' a '</explanation>';

	*put '            <explanation>' translate(expl_base,'-','/') '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '    </dynamicPricingFactorPolicyCoverLangList>';
	put ' </dynamicPricingFactorPolicyCover>';

Re: Delete the empty space in XML file

joao_moreira — Tue, 20 Mar 2018 11:11:46 GMT

Hi,

Something like this would solve the issue:

if dpf_base eq "" then do;

put '    <dynamicPricingFactor></dynamicPricingFactor>';
end;
else do;
put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>';
end;

Or are you looking for something else?

Best Regards,

Joao Moreira.

Re: Delete the empty space in XML file

Babloo — Tue, 20 Mar 2018 11:39:00 GMT

It´s not working as in the output XML file I see as <dynamicPricingFactor>.</dynamicPricingFactor>. I placed the following code before the code snippet which I gave in the initial post. Appreciate if could help me further.

	if dpf_base eq "" then do;

put '    <dynamicPricingFactor></dynamicPricingFactor>';
end;
else do;
put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>

Re: Delete the empty space in XML file

joao_moreira — Wed, 21 Mar 2018 15:50:00 GMT

I assume that's because dpf_base is numeric. Is that rigth?

If so the code should be:

if dpf_base eq . then do;
put '    <dynamicPricingFactor></dynamicPricingFactor>';
end;
else do;
put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>';
end;

Best Regards,

Joao Moreira

EDIT (@Reeza) correct a double quote to single quote.

Re: Delete the empty space in XML file

Patrick — Tue, 20 Mar 2018 12:24:04 GMT

or you use the missing() function which works for both numeric and character

if missing(dpf_base eq) then do;

Instead of writing this XML using data step put statements it might also be worth testing if the libname XMLV2 engine could generate an acceptable result.

libname test xmlv2 'c:\test\test.xml';
data test.class;
  set sashelp.class;
  if _n_=2 then call missing(name);
run;

Re: Delete the empty space in XML file

Babloo — Tue, 20 Mar 2018 13:03:10 GMT

Could you please help me to resolve this error?

60         		if missing(dpf_base eq) then
                                   _
                                   390
                                   76
ERROR 390-185: Expecting an relational or arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

61         do;
62         put '    <dynamicPricingFactor></dynamicPricingFactor>';
63         end;
           ___
           161
ERROR 161-185: No matching DO/SELECT statement.

Re: Delete the empty space in XML file

Babloo — Tue, 20 Mar 2018 13:21:43 GMT

Still the issue seem to be not resolved. I just compared the output after (right) and before (left) updating the code and screenshot below for quick reference. I´m getting two extra lines (red highlighted in right side) as you seen below.

Code which I added is,

if dpf_base eq . then do;
put '    <dynamicPricingFactor></dynamicPricingFactor>';
end;

Output Comparision:

Re: Delete the empty space in XML file

Tom — Tue, 20 Mar 2018 14:42:21 GMT

The MISSING() function just takes a value. The EQ keyword does not make sense there.

if missing(dpf_base) then

Re: Delete the empty space in XML file

Babloo — Tue, 20 Mar 2018 15:00:37 GMT

I think the tricky part is where we adding that if clause. Could you please
help me understand where should I add the If clause? Is it at the
beginning of the code or in between the code which I shared in the initial
post?

Re: Delete the empty space in XML file

Reeza — Tue, 20 Mar 2018 15:16:22 GMT

You add the clause where you output the statement.

IF <> then do;

put ...

end;

else do;

put ...

end;

@Babloo wrote:
I think the tricky part is where we adding that if clause. Could you please
help me understand where should I add the If clause? Is it at the
beginning of the code or in between the code which I shared in the initial
post?

Re: Delete the empty space in XML file

Patrick — Tue, 20 Mar 2018 23:11:48 GMT

@Babloo

Please post sample data for your code so we can provide an actually tested solution.

Re: Delete the empty space in XML file

ChrisNZ — Tue, 20 Mar 2018 23:47:48 GMT

Additional lines make no sense.

You replace one put statement with another, so you'll have the same number of lines.

if missing(dpf_base) then 
  put '    <dynamicPricingFactor></dynamicPricingFactor>';
else 
  put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>";

instead of

put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>";

Re: Delete the empty space in XML file

Reeza — Wed, 21 Mar 2018 00:28:04 GMT

SAS OPTIONS go outside of the PROC. Put the option before the data step and it should work fine.

Im assuming you have data step code somewhere since none is shown.

Re: Delete the empty space in XML file

Patrick — Wed, 21 Mar 2018 00:51:16 GMT

@Babloo

Here another coding option:

data have;
  dpf_base='AAA';
  output;
  dpf_base=' ';
  output;
  stop;
run;

data _null_;
  file print;
  set have;
  move= -1-missing(dpf_base);
  put '    <dynamicPricingFactor>' dpf_base +move '</dynamicPricingFactor>';
run;

Result:

    <dynamicPricingFactor>AAA</dynamicPricingFactor>
    <dynamicPricingFactor></dynamicPricingFactor>

Re: Delete the empty space in XML file

Babloo — Wed, 21 Mar 2018 10:09:28 GMT

Still I'm not receiving the desired output. In real time, the variables (numeric) DPF_base, DPF_major_change and DPF_Minor_Change are not filled in SAS dataset then the field: <dynamicPricingFactor> in the xml file is filled in with a ‘.’ The below conversion program will do this automatically and this not the good way to fill it, it should be left empty as <dynamicPricingFactor></dynamicPricingFactor>

So please guide me where should I add your proposed solution in my code below? If I add it after the put statement I'm not receiving the desired output as the number of output lines after adding the code rose from 12327 lines to 12567.

put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>';

My condition is actual output lines in the code should be always be 12327 even after executing the code after the changes.

data _null_;
	set input.dpf_cur_mon end=fin;
	by externalidentifier;
	length a$200 b$200 c$300;
	file "/shared/xml/dpf_cur_mon.xml";
		if _n_=1 then
		do;
			put '<?xml version="1.0" encoding="UTF-8"?>';
  			end;

	if first.externalidentifier then
		do;
            put '<dynamicPricingFactorPolicy> ';
			put '<policyRef>';
			put '<externalIdentifier>' externalidentifier +(-1)'</externalIdentifier>';
			put '</policyRef>';
			put '<dateRenewal>' dateRenewal+(-1)'</dateRenewal>';
			put '<clauseCode>' clause_Base+(-1)'</clauseCode>';
			put '<dynamicPricingFactorPolicyCoverList>';
		end;

	put '<dynamicPricingFactorPolicyCover>';
	put   '<productVersionCoverRef>';
	put '    <externalIdentifier>'  cover +(-1) '</externalIdentifier>';
	put '   </productVersionCoverRef>';
	put '    <isForNonMatch>false</isForNonMatch>';
	put '    <hashCode1>'  veh_driver_hashcode +(-1)  '</hashCode1>';
	put '    <hashCode2>' version_subversion_hashcode +(-1) '</hashCode2>';
	put '    <dynamicPricingFactor>' dpf_base +(-1) '</dynamicPricingFactor>';
	put '    <dynamicPricingFactorPolicyCoverLangList>';
	put '       <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>FR</externalIdentifier>';
	put '            </languageRef>';
	a=translate(expl_base,'-','/');
	put '            <explanation>' a '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '        <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>VLS</externalIdentifier>';
	put '            </languageRef>';
	put '            <explanation>' a '</explanation>';

	*put '            <explanation>' translate(expl_base,'-','/') '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '    </dynamicPricingFactorPolicyCoverLangList>';
	put ' </dynamicPricingFactorPolicyCover>';
	put '<dynamicPricingFactorPolicyCover>';
	put   '<productVersionCoverRef>';
	put '    <externalIdentifier>'  cover +(-1) '</externalIdentifier>';
	put '   </productVersionCoverRef>';
	put '    <isForNonMatch>true</isForNonMatch>';
	put '    <hashCode1>'  veh_driver_hashcode +(-1)  '</hashCode1>';
	put '    <hashCode2></hashCode2>';
	put '    <dynamicPricingFactor>' dpf_minor_change +(-1)'</dynamicPricingFactor>';
	put '    <dynamicPricingFactorPolicyCoverLangList>';
	put '       <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>FR</externalIdentifier>';
	put '            </languageRef>';
	b=translate(expl_minor_change,'-','/');
	put '            <explanation>' b '</explanation>';

	*put '            <explanation>' translate(expl_major_change,'-','/') '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '        <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>VLS</externalIdentifier>';
	put '            </languageRef>';

	*put '            <explanation>' translate(expl_major_change,'-','/') '</explanation>';
	put '            <explanation>' b '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '    </dynamicPricingFactorPolicyCoverLangList>';
	put ' </dynamicPricingFactorPolicyCover>';
	put '<dynamicPricingFactorPolicyCover>';
	put   '<productVersionCoverRef>';
	put '    <externalIdentifier>'  cover +(-1)  '</externalIdentifier>';
	put '   </productVersionCoverRef>';
	put '    <isForNonMatch>true</isForNonMatch>';
	put '    <hashCode1></hashCode1>';
	put '    <hashCode2></hashCode2>';
	put '    <dynamicPricingFactor>' dpf_major_change +(-1)  '</dynamicPricingFactor>';
	put '    <dynamicPricingFactorPolicyCoverLangList>';
	put '       <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>FR</externalIdentifier>';
	put '            </languageRef>';
	c=translate(expl_major_change,'-','/');
	put '            <explanation>' c '</explanation>';

	*put '            <explanation>' translate(expl_minor_change,'-','/') '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '        <dynamicPricingFactorPolicyCoverLang>';
	put '            <languageRef>';
	put '                 <externalIdentifier>VLS</externalIdentifier>';
	put '            </languageRef>';
	put '            <explanation>' c '</explanation>';
	put '        </dynamicPricingFactorPolicyCoverLang>';
	put '    </dynamicPricingFactorPolicyCoverLangList>';
	put ' </dynamicPricingFactorPolicyCover>';

	if last.externalidentifier then
		do;
			put '</dynamicPricingFactorPolicyCoverList>';
			put '</dynamicPricingFactorPolicy>';
		end;

	if fin then
		do;
			put	'</dynamicPricingFactorPolicyList>';
			end;
run;

Sample data:

externalIdentifier

dateRenewal

Veh_Driver_hashcode

Version_subversion_hashcode

Clause_Base

Clause_Major_change

Clause_Minor_change

Cover

DPF_base

Expl_base

DPF_major_change

Expl_major_change

DPF_minor_change

Expl_minor_change

9000345833

12/03/2119

31_834261303

1.0

ClauseBase1

ClauseMajor1

ClauseMinor1

covMotorbikeBA

FR BA x2 perextra/NL BA x2 perc extra

FR BA x0,5 pe xtra/NL BA x0,5 perc extra

FR BA x1,5 prc xtra/NL BA x1,5 perc extra

Re: Delete the empty space in XML file

Patrick — Wed, 21 Mar 2018 11:17:38 GMT

@Babloo

If you want me to run your code with the data you've posted then please post this data in a friendlier form (as SAS data step creating the data).

The code I've posted should work for both character and numeric variables. Below the same approach for two numeric variables. I hope that will answer your question where and how in the code to populate and use variable move.

data have;
  dpf_base=999; dpf_major_change=.; output;
  dpf_base=.; dpf_major_change=123; output;
  stop;
run;

data _null_;
  file print;
  set have;
  move= -1-missing(dpf_base);
  put '    <dynamicPricingFactor>' dpf_base +move '</dynamicPricingFactor>';
  move= -1-missing(dpf_major_change);
  put '    <dynamicPricingFactor>' dpf_major_change +move '</dynamicPricingFactor>';
run;

Result:

    <dynamicPricingFactor>999</dynamicPricingFactor>
    <dynamicPricingFactor></dynamicPricingFactor>
    <dynamicPricingFactor></dynamicPricingFactor>
    <dynamicPricingFactor>123</dynamicPricingFactor>

Re: Delete the empty space in XML file

Babloo — Wed, 21 Mar 2018 13:10:50 GMT

I just wanted to know where I've to (in my code) add your proposed code to resolve the issue. Given below the data step which creates the data after placing the file in the location where you have access to import.

DATA INPUT.dpf_bcar_bmotorbike_201803;
    LENGTH
        externalIdentifier $ 11
        dateRenewal      $ 10
        Veh_Driver_hashcode $ 19
        Version_subversion_hashcode $ 3
        Clause_Base      $ 11
        Clause_Major_change $ 12
        Clause_Minor_change $ 12
        Cover            $ 20
        DPF_base           8
        Expl_base        $ 65
        DPF_major_change   8
        Expl_major_change $ 45
        DPF_minor_change   8
        Expl_minor_change $ 69 ;
    FORMAT
        externalIdentifier $CHAR11.
        dateRenewal      $CHAR10.
        Veh_Driver_hashcode $CHAR19.
        Version_subversion_hashcode $CHAR3.
        Clause_Base      $CHAR11.
        Clause_Major_change $CHAR12.
        Clause_Minor_change $CHAR12.
        Cover            $CHAR20.
        DPF_base         F12.4
        Expl_base        $CHAR65.
        DPF_major_change F12.4
        Expl_major_change $CHAR45.
        DPF_minor_change F12.4
        Expl_minor_change $CHAR69. ;
    INFORMAT
        externalIdentifier $CHAR11.
        dateRenewal      $CHAR10.
        Veh_Driver_hashcode $CHAR19.
        Version_subversion_hashcode $CHAR3.
        Clause_Base      $CHAR11.
        Clause_Major_change $CHAR12.
        Clause_Minor_change $CHAR12.
        Cover            $CHAR20.
        DPF_base         BEST12.
        Expl_base        $CHAR65.
        DPF_major_change BEST12.
        Expl_major_change $CHAR45.
        DPF_minor_change BEST12.
        Expl_minor_change $CHAR69. ;
    INFILE '/GTU/AGSR/wrk1/SAS_workBCE300000AD2_dlu0lsas009g.linux.dv.be/#LN00016'
        LRECL=294
        ENCODING="LATIN1"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        externalIdentifier : $CHAR11.
        dateRenewal      : $CHAR10.
        Veh_Driver_hashcode : $CHAR19.
        Version_subversion_hashcode : $CHAR3.
        Clause_Base      : $CHAR11.
        Clause_Major_change : $CHAR12.
        Clause_Minor_change : $CHAR12.
        Cover            : $CHAR20.
        DPF_base         : BEST32.
        Expl_base        : $CHAR65.
        DPF_major_change : BEST32.
        Expl_major_change : $CHAR45.
        DPF_minor_change : BEST32.
        Expl_minor_change : $CHAR69. ;
RUN;

Re: Delete the empty space in XML file

Reeza — Wed, 21 Mar 2018 14:42:21 GMT

The code you've posted is an import data step, whereas the code you initially posted was a list of PUT statements more likely to export data. You've switched the problem and code around here somewhere....