topic Re: Why don't PROC SQL and Data Step create same Macro Variable in this case? in SAS Programming

Why don't PROC SQL and Data Step create same Macro Variable in this case?

PaigeMiller — Tue, 09 Jul 2013 13:14:52 GMT

I have the outputs of PROC CONTENTS, and I am trying to create a macro variable of something in the NAME column of the PROC CONTENTS output. I try this via a DATA step, and also via PROC SQL.

The following code, working on the same data set, should produce two macro variables with the exact same values (in my opinion), but it does not. There are extra blanks appended to the second macro variable. Why? How can I fix this so PROC SQL produces a macro variable without the extra blanks?

data _null_;

set _cont_(where=(upcase(name)='PARAM_NUM' or upcase(name)='PARM_NUM'));

call symputx('parm_num1',name);

run;

proc sql noprint;

select distinct trim(name) into :parm_num2 from _cont_ where upcase(name) eqt 'PARAM_NUM' or upcase(name) eqt 'PARM_NUM';

quit;

%put PARM_NUM1 &parm_num1 ****;

%put PARM_NUM2 &parm_num2 ****;

The results I get from the PUT statements show the values are different with PARM_NUM2 appearing to have extra blanks:

1110 %put PARM_NUM1 &parm_num1 ****;

PARM_NUM1 parm_num ****

1111 %put PARM_NUM2 &parm_num2 ****;

PARM_NUM2 parm_num ****

Re: Why don't PROC SQL and Data Step create same Macro Variable in this case?

data_null__ — Tue, 09 Jul 2013 13:40:36 GMT

Use TRIMMED option

17         proc sql;
18            select name into :name1 from sashelp.class(obs=1);
19            %put NOTE: name1=*&name1*;
NOTE: name1=*Alfred  *
20            select name into :name2 trimmed from sashelp.class(obs=1);
21            %put NOTE: name2=*&name2*;
NOTE: name2=*Alfred*
22            quit;

Re: Why don't PROC SQL and Data Step create same Macro Variable in this case?

Ksharp — Tue, 09 Jul 2013 14:06:23 GMT

or using

select distinct trim(name) into :parm_num2 separated by ' ' from _cont_

Ksharp

Re: Why don't PROC SQL and Data Step create same Macro Variable in this case?

UrvishShah — Wed, 10 Jul 2013 04:49:54 GMT

Hi,

By Default, CALL SYMPUT will maintain any leading or trailling blanks...In below example program, i have created name which have 20 bytes length but originally there is only 3 bytes so name will have 17 extra trailling blanks which is going to retain while creating macro variable by using CALL SYMPUT during data step execution...

But if you use CALL SYMPUTX insted of CALL SYMPUT then it will remove any leading or trailling blanks from the variable...Here, CALL SYMPUT "X" represents EXTRA BLANKS to be remove and in contrast, PROC SQL will retain any leading or trailing blanks while creating macro variable during proc sql execution...

SEE THE BELOW EXAMPLE PROGRAME...HOPE IT HELPS...

data test;

length name $20.;

name = "XYZ";

call symputx("data_X_name",name);

call symput("data_name",name);

run;

proc sql noprint;

select name into :sql_name

from test;

quit;

%put CALL SYMPUT will resolve to: &data_name.*****;

%put CALL SYMPUTX will resolve to: &data_x_name.*****;

%put PROC SQL will resolve to: &sql_name.*****;

-Urvish

Re: Why don't PROC SQL and Data Step create same Macro Variable in this case?

PaigeMiller — Wed, 10 Jul 2013 12:16:08 GMT

Great stuff, guys, thank you very much.