Neat. But how confident are we that this will scale to 9000+ data sets? How about a hash-based solution, so that all of the housekeeping occurs at run time? ... View more

What is "gummed up"? I ran data sumpim / view=sumpim ; key = 'a' ; satellite_1 = 11 ; output ; key = 'b' ; satellite_1 = 12 ; output ; run ; data nuttin / view=nuttin ; key = 'c' ; satellite_2 = 21 ; stop ; run ; data merged ; merge sumpim nuttin ; by key ; run ; and found that it prodeced a sensible result. It is true that there can be satellite variables which are always missing (like SATELLITE_2 in this example), but it may be easier to allow those to accrue and clean them up after the merges are performed. Mishka1 wrote: So I've found a million ways to return the number of observations in a data set but a view seems to be understandably harder. I have a library full of views and I need to merge them but some of the views don't return anything so it gums up my merge. So far, I found out how to only pull the views that exist in the library. How do I exclude the ones that will return nothing? Proc SQL noprint;       Select 'temp.' || memname as ViewName       into :ViewNames separated by ' '       From DICTIONARY.VIEWS       Where UPCASE(LIBNAME)='TEMP'       and UPCASE(MEMTYPE)='VIEW' ; quit; data Combined;       set &ViewNames;         run; Thanks as always! ... View more

This should do it in one step: data want(drop = Type_Combined Population) ; do until (last.Location) ;    set have ;    by Location ;    Type = substr(Type_Combined,1,length(Type_Combined)-4) ;    select ( scan(Type_Combined,-1,'_') ) ;       when ('Num') Numerator   = Population ;       when ('Den') Denominator = Population ;       otherwise ;       end ;    end; run ; ... View more

This could be simpler if the canned queries incorporated the IDs, as in   cards; Select "Qry1" as QueryName, Field1, Field2 From work.trans Where Field2= 'blue' Select "Qry2" as QueryName, Field1, Field2 From work.trans Where Field2= 'red' ; Also, why not use RESET to manage the state of the PRINT/NOPRINT option so that only one SQL step is needed. ... View more

I think MODIFY is definitely the answer. Tom's code might fail however if the data set initially has no observations. I would move the MODIFY statement to the end, right after the STOP ststement. Tom wrote: Look at the MODIFY statement. proc sql; insert into tb.want  (code, age) values (3, 27); quit; data tb.want;   modify tb.want;   code=3;   age=27;   output;   stop; run; ... View more

I was talking about the BEST informat, not format, or at least that was my intent. art297 wrote: I agree but with one change that Howles pointed out a couple of weeks ago.  We really ought to stop recommending using "best" as a format as it is really just a substitute for the true value (e.g., 32.) and calling it best leads one to think that it is doing something special. ... View more

I thin you are working too hard :-). create table new as select *   from class order by missing(name) , name ; MikeZdeb wrote: Hi ... here are a couple other ideas  ... * some data with missing values; data class; set sashelp.class; name = ifc(ranuni(999) le .3, ' ', name); run; * sort non-missing; proc sort data=class (where=(^missing(name))) out=new; by name; run; * add missing at end; proc append base=new data=class(where=(missing(name))); run; Or ... * sort on a new variable within SQL, then drop it; proc sql; create table new (drop=_x) as select *, 1 as _x from class (where=(^missing(name))) union select *, 2 as _x from class (where=(missing(name))) order by _x, name; quit; ... View more

Also get rid of the "best" in the informat. It's just an alias, one which reinforces the misconception that something is being optimized. Beneficiary_Account=cats(input(Beneficiary_Account,32.)); MikeZdeb wrote: Hi ... just a suggestion about Art's code, this Beneficiary_Account=strip(put(input(Beneficiary_Account,best32.),best32.)); can be replaced by Beneficiary_Account=cats(input(Beneficiary_Account,best32.)); ... View more