topic Re: PROC SQL INTO: order of values when creating a macro variable out of a character in SAS Programming

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

Natalie — Mon, 24 Jun 2013 07:10:41 GMT

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

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

RichardinOz — Mon, 24 Jun 2013 09:08:13 GMT

Have you tried

PROC SQL NOPRINT;

SELECT DISTINCT UPCASE(name)

INTO :ListOfVars SEPARATED BY "# "

FROM contents

ORDER BY varnum;

QUIT;

Richard

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

Natalie — Mon, 24 Jun 2013 09:22:31 GMT

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

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

Astounding — Mon, 24 Jun 2013 13:27:21 GMT

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.

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

RichardinOz — Mon, 24 Jun 2013 13:50:22 GMT

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

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

esjackso — Mon, 24 Jun 2013 14:30:19 GMT

Should it not be order by Name instead of varname?

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

ballardw — Mon, 24 Jun 2013 22:27:26 GMT

Maybe before the SQL proc sort using options

sortseq=linguistic (numeric_collation=on)

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

Tom — Tue, 25 Jun 2013 01:37:29 GMT

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;

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

Ksharp — Tue, 25 Jun 2013 01:39:20 GMT

Here is:

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

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

Natalie — Tue, 25 Jun 2013 05:47:28 GMT

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

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

Natalie — Tue, 25 Jun 2013 05:50:42 GMT

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

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

Ksharp — Tue, 25 Jun 2013 06:32:21 GMT

OK.

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

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

sreyasg — Thu, 17 Aug 2023 12:33:14 GMT

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