PROC SQL INTO: order of values when creating a macro variable out of a character

Reply
Contributor
Posts: 28

PROC SQL INTO: order of values when creating a macro variable out of a character

Dear all,

I have a quite simple step in my progam, where I create a macro variable which should contain values of the variable NAME (character).

contents.png

PROC SQL NOPRINT;

      SELECT DISTINCT UPCASE(name)

      INTO   :ListOfVars    SEPARATED BY "# "

      FROM   contents;

QUIT;

As a result I get the values of the NAME variable sorted as => F1, F10, F11 etc.

=====> List of variables          : F1# F10# F11# F2# F3# F4# F5# F6# F7# F8# F9

I would like PROC SQL to create the list of values in the sequence as the values are sorted in the dataset.

When adding a sorting variable VARNUM, I get the ordering I need, but also the WARNING "INTO clause specifies fewer host variables than columns listed in the SELECT clause."

PROC SQL NOPRINT;

      SELECT DISTINCT UPCASE(name),            varnum

      INTO   :ListOfVars    SEPARATED BY "# "

      FROM   contents

      ORDER BY varnum;

QUIT;

=====> List of variables          : F1# F2# F3# F4# F5# F6# F7# F8# F9# F10# F11

For the time being, I added a macro variable name also for the VARNUM to avoid the WARNING, but I was wondering, if there is a way, maybe an option to control the ordering of values?

Thank you in advance,

Natalie

Attachment
Super Contributor
Posts: 644

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Have you tried

PROC SQL NOPRINT;

      SELECT DISTINCT UPCASE(name)

      INTO   :ListOfVars    SEPARATED BY "# "

      FROM   contents

      ORDER BY varnum;

QUIT;


Richard

Contributor
Posts: 28

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Posted in reply to RichardinOz

Hi Richard,

yes, it was my first idea, but then SAS brings the warning, that it can't find the variable in the SELECT-Statement:

 

WARNING: The query as specified involves ordering by an item that doesn't appear in its

SELECT clause. Since you are ordering the output of a SELECT DISTINCT it may

appear that some duplicates have not been eliminated.

Actually, I think now the problem ist the DISTINCT selection. I removed DISTINCT (which I actually do not need at this step) and it runs ok!

Thanks,

Natalie

Super User
Posts: 5,498

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

There might be a tricky way to do this in one step.  But since your data sets will be small, I see no reason to make everything finish in one step instead of two.  You could try:

proc sql noprint;

   create table contents2 as select distinct name, varnum from contents order by varnum;

   select strip(name) into : ListofVars separated by '# ' from contents2;

quit;

Note that DISTINCT was removing leading and trailing blanks, so the second SELECT applies the STRIP function.

Good luck.

Super Contributor
Posts: 644

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Posted in reply to RichardinOz

I would still expect you to get the warning

WARNING: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

However, that's just there to check you know what you are doing.  In this context it can be ignored.

Another thought - maybe the DISTINCT was responsible for the unwanted order - SAS SQL will order output if it has to group, and you could argue DISTINCT is a form of grouping.  So try it without the DISTINCT and without the ORDER  BY.

Richard

Contributor
Posts: 28

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Posted in reply to RichardinOz

Hi Richard,

yes, you are right, it's the DISTINCT-Statement, SAS needs to sort the variable and therefore I get the NAMEs in the macro variable as F1# F10# F11# F2# F3# F4# F5# F6# F7# F8# F9.

Adding VARNUM to the ORDER-Statement did not bring any WARNINGs:

PROC SQL NOPRINT;

      SELECT UPCASE(name)

      INTO   :ListOfVars    SEPARATED BY "# "

      FROM   contents

      ORDER BY varnum;

QUIT;

%PUT %====> &ListOfVars.;

Alternatively, I can create distinct list and sort it beforehand and in the 2nd step create the macro variable, it then should be fine. But as I work with PROC CONTENTS to get the variable names, I realilsed now, that the DISTINCT selection is actually not necessary Smiley Happy

Natalie

Super Contributor
Posts: 334

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Posted in reply to RichardinOz

Should it not be order by Name instead of varname?

EJ

Super User
Posts: 11,343

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Maybe before the SQL proc sort using options

 

sortseq=linguistic (numeric_collation=on)


Super User
Super User
Posts: 7,039

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Adding the VARNUM to the SELECT list will suppress the warning, but you need to give it a macro variable name to write into.

PROC SQL NOPRINT;

  SELECT UPCASE(name)

       , varnum

    INTO :ListOfVars SEPARATED BY "# "

        , :dummy

    FROM contents

    ORDER BY varnum

  ;

QUIT;

Super User
Posts: 10,020

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Here is:

ORDER BY input(substr(name,2),best8.);

Contributor
Posts: 28

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

Hi Ksharp,

thanks, good idea Smiley Happy....but I'm working on a macro, where I need to go through variables of a dataset and and they could be named different than f1-fx (in this case I read in an excel file with GETNAMES=NO).

Natalie


Super User
Posts: 10,020

Re: PROC SQL INTO: order of values when creating a macro variable out of a character

OK.

ORDER BY input(compress(name, ,'kd'),best8.);

Ask a Question
Discussion stats
  • 11 replies
  • 2792 views
  • 1 like
  • 7 in conversation