BookmarkSubscribeRSS Feed
Natalie
Calcite | Level 5

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

12 REPLIES 12
RichardinOz
Quartz | Level 8

Have you tried

PROC SQL NOPRINT;

      SELECT DISTINCT UPCASE(name)

      INTO   :ListOfVars    SEPARATED BY "# "

      FROM   contents

      ORDER BY varnum;

QUIT;


Richard

Natalie
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

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

esjackso
Quartz | Level 8

Should it not be order by Name instead of varname?

EJ

ballardw
Super User

Maybe before the SQL proc sort using options

 

sortseq=linguistic (numeric_collation=on)


Tom
Super User Tom
Super User

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
Super User

Here is:

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

Natalie
Calcite | Level 5

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


Ksharp
Super User

OK.

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 10033 views
  • 1 like
  • 8 in conversation