I do this sort of thing frequently on Unix (Solaris actually). The following script shows the fundamentals: #!/usr/bin/ksh sas9 sqlquery1 & sas9 sqlquery2 & wait sas9 sqlquery3 sas9 sqlquery4 the first line announces that it is a Korne shell script and that the OS should use the Korne shell interpreter (ksh) located at /usr/bin/ksh. The second line invokes the SAS executable (called sas9 on our machine but may be different on yours) and the ampersand following the sas9 invocation says to run in the background. Likewise for the next program sqlquery2. The wait command says to wait until background processes are finished then proceed. The following two invocations of sas are done sequentially since they are not followed by an ampersand. If running under Windows then none of the above applies ... View more

A far more relevant criticism would be that I use two macro variables that were not explicitly declared to be local and thereby have the potential to influence the parent environment. I should have added the following line: %local var fmt; ... View more

you're kidding me right? In an example designed to show formatting a list of variables with a list of formats you're fussing about which formats are used? I suppose I could have used a custom format, undefined and unaddressed in the request - or some other character format native to SAS but certainly not the type of format that was expressly specified in the request!!?? ... View more

Here's a macro and a sample of how to use it that should help: options mprint nosource; %macro Make_Fmt_Stmts( varlist, fmtlist );    %local done i;    %let done = notyet;    %do %until(&done=DONE);       %let i = %eval( &i +1 );       %let var = %qscan( &varlist, &i, %str( ));       %let fmt = %qscan( &fmtlist, &i, %str( ));       %if ( %length(&var) = 0 ) %then %let done = DONE;       %else  format &var &fmt %str(;);    %end; %mend; data new;    set sashelp.class;    %Make_Fmt_Stmts( name sex age height weight, $9. $1. 3. 6.2 6.2 ) run; proc contents data=new; proc print data=new; run; ... View more

True that in a macro do loop, leading blanks in the value of NOB won't matter, but he wanted to use the value in: proc sql noprint;     select rate        into :alp1 - :alp&nob.     from mydata; quit; where leading blanks will matter and cause an error as he said: "I am getting an error at the step 'proc sql'" ... View more

It makes the difference between actually working or failure - if &nob includes leading blanks (and it will using symput rather than symputx) then when &nob is used as attempted above, i.e. proc sql noprint;     select rate        into :alp1 - :alp&nob.     from mydata; quit; then the "select" would resolve into: "select into :alp1 - alp      100 from mydata;" - and this will fail due to the leading blanks. What is needed in the above is "alp1 - alp100" with no leading blanks ahead of the numeric value. In a %do %end loop the string value of &nob won't matter (as long as its an integer). Solutions above that will actually work are shivas, Tom, Linlin and my earlier suggestion (more than one way to write SAS code). ... View more

The problem comes with the value stored in nob, it will be a string with the value "bbbbb100" (where b=blank). Try this trick: proc sql noprint;     select count(*) into :nob separated by ' ' from mydata; quit; ... View more

Agree completely - when I need sums or means or ... its almost always proc summary or proc means (when I want quantiles, or medians, etc). I write less code using proc summary than the equivalent code using SQL, however, SQL can sometimes do what it may take several steps otherwise. With the simple problem presented, I would prefer proc summary, but he asked in the framework of proc sql so ... ... View more

I would agree that its going to help only a little bit - computers are very fast these days - but the question was about which is faster if then else vs select when - the difference between these options I suspect is going to be miniscule - greater savings (however small) will be obtained by ordering the test conditions. ... View more

The following solution uses your table of column names which I called "datasetofcolnames" in the code below: [pre] %macro sumCols( _collist ); %local return n_cols colname i; %let n_cols = %sysfunc( countw( &_collist, %str( ))); %do i=1 %to &n_cols; %let colname = %scan( &_collist, &i, %str( )); %let return = &return sum(&colname) as &colname; %if ( &i < &n_cols ) %then %let return = &return,; %end; &return %mend; proc sql noprint; *-- first get a list of the column names ; select distinct colname into : collist separated by ' ' from datasetofcolnames order by colname; *-- then extract your data ; create table testMacro as select row_labels, month, %sumCols( &collist ) from Me group by row_labels, month; quit; [/pre] ... View more

I absolutely agree with data _null_ - I would want to avoid 1000 datasetps & 1000 proc regs - there is almost always a better solution by restructuring the problem - one way being the one suggested - however, there are rare times when you just gotta bite the bullet and muscle through the 1000 or more steps. ... View more