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
Have you tried
PROC SQL NOPRINT;
SELECT DISTINCT UPCASE(name)
INTO :ListOfVars SEPARATED BY "# "
FROM contents
ORDER BY varnum;
QUIT;
Richard
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
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.
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
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
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
Should it not be order by Name instead of varname?
EJ
Maybe before the SQL proc sort using options
sortseq=linguistic (numeric_collation=on)
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;
Here is:
ORDER BY input(substr(name,2),best8.);
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
OK.
ORDER BY input(compress(name, ,'kd'),best8.);
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!
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.
Ready to level-up your skills? Choose your own adventure.