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

[Natalie]

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).

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

[RichardinOz]

Have you tried

PROC SQL NOPRINT;

      SELECT DISTINCT UPCASE(name)

      INTO   :ListOfVars    SEPARATED BY "# "

      FROM   contents

      ORDER BY varnum;

QUIT;

Richard

[Natalie]

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

[Astounding]

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.

[sreyasg]

Yes , you are right using distinct, invoked sorting of values , 

If we want values to be inserted into macro variables in same order as in the dataset, don't use distinct.

It worked for me 

[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

[Natalie]

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

Natalie

[esjackso]

Should it not be order by Name instead of varname?

EJ

[ballardw]

Maybe before the SQL proc sort using options

sortseq=linguistic (numeric_collation=on)

[Tom]

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;

[Ksharp]

Here is:

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

[Natalie]

Hi Ksharp,

thanks, good idea ....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

[Ksharp]

OK.

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