comp.soft-sys.sas - The SAS statistics package.
This does what you want for the sample data given, making lots of possibly unwarranted assumptions, but something tells me it isn't what your client wants: =====proc sql; create table three as select * from ( select key, max(var) as var, case 1 when (var=min(var) and var ne max(var)) then resolve('%let hold=' || var || ';') when (var ne min(var) and var ne max(var)) then resolve('%let hold=&HOLD. ' || var || ';') when (var eq min(var) and var eq max(var)) then var else trim(symget('HOLD')) || ' ' || var end as newvar from one (sortedby = key var) group by key ) group by key having newvar = max(newvar) order by key, var; quit; ===== Scott Bass wrote: > Hi, > > I would think I would have known how to do this by now, but alas I don't :- > ( > > Say I have the following data step: > > data one; > length key 8 var $1; > input key var; > cards; > 1 A > 1 B > 1 C > 2 D > 2 E > 3 F > ; > run; > > data two; > set one; > by key; > length newvar $200; > retain newvar; > if first.key then newvar = ""; > newvar = catx(" ",newvar,var); > if last.key then output; > run; > > Is there a way to create this dataset using PROC SQL only? I'd rather > just use the data step, but the customer would prefer SQL if possible. > > Thanks, > Scott
I intend to dynamically use a string in the PROC SQL where clause..I have dipped my hands into Macro quoting to achieve this and have a few questions.. given a list of variables defined in %variables= var1 var2 var3;, I want to execute.. PROC SQL; select name from <data> where name in ('var1','var2','var3'); quit; run; To dynamically code the string 'var1','var2',.... I have tried to code the following macro. %let count = 3; /* a counter set to 3 because we know there are three variables at present */ %macro mac1; %let i = 1; %let step = %scan(&variables,&i); %let code =; %do %while (%length(&step)>0); %if &i < &count %then %do; %let code = &code%nrquote(%bquote('))&step%nrquote(%bquote('))%str(,); %end; %if &i = &count %then %do; %let code = &code%nrquote(%bquote('))&step%nrquote(%bquote(')); %end; %let i = %eval(&i + 1); %let step = %scan(&variables,&i); %end; &code %mend mac1; Upon invoking this macro as PROC SQL select name from <data> where name in (%mac1); quit; I get errors.. However, when I look at the log -- using %put &code, instead of &code -- I see the exact statements that I want, i.e. 'var1','var2',var3' Thanks, Rushi
I need to union(concatenate) 9-10 datasets. Each has same number of variables and all the variables are same. I am wondering what is the quickest way to union(concatenate) a big number of datasets? Proc SQL can use UNION statement like: Proc SQL; (select * from a) union (select * from b) union (select * from c); quit; But can I union 9-10 datasets at one time? Would that take long time? If I use Proc Append? I can only append one dataset at a time, right? Would it be more time consuming? Thanks, Mark