DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,913
Accepted Solution

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

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 Smiley Tonguearm_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                         ****


Accepted Solutions
Solution
‎07-09-2013 09:40 AM
Respected Advisor
Posts: 3,799

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

Posted in reply to PaigeMiller

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;

View solution in original post


All Replies
Solution
‎07-09-2013 09:40 AM
Respected Advisor
Posts: 3,799

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

Posted in reply to PaigeMiller

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;
Super User
Posts: 10,023

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

Posted in reply to PaigeMiller

or using

select distinct trim(name) into Smiley Tonguearm_num2 separated by ' '  from _cont_

Ksharp

Regular Contributor
Posts: 195

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

Posted in reply to PaigeMiller

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

Trusted Advisor
Posts: 1,913

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

Posted in reply to UrvishShah

Great stuff, guys, thank you very much.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 236 views
  • 4 likes
  • 4 in conversation