Try This :    proc sql; create table Table_1 as select t.*,b.inst from t left join b on a.id = b.id; quit;   or you can also try with inner join, as per our required logic. ... View more

I'd like to help. If you tell us what you are trying to accomplish, it would be easier. ... View more

Building on DN and Patrick's input : data tmp;    length value flag $8;    input value $&;    a = input(value,?? comma8.);    format a best8.;    flag=ifc(missing(a), 'string', 'numeric'); datalines; 1234 -1234 +1234 1.3 1e-3 3d2 4,000 -4,000.9 sys ; proc print data=tmp noobs; run; PG ... View more

If you cannot get MINOPERATOR options to work for you (or just because it is easier ) you can use %SYSFUNC() to use the INDEXW() function. %macro t; %local class_vards; proc contents noprint data=sashelp.class out=class_vards;run; proc sql noprint; select name into: varlst separated by ',' from class_vards; quit; %if %sysfunc(indexw(%qupcase(&varlst),AGE,%str(,))) %then %put "it is in the list"; %else %return; %mend; %t; Other points: Watch out for case when searching, you might want to add select UPCASE(NAME) in the SQL code. Note that you do NOT want the double quotes as there will not be quotes in the value of the macro variable that PROC SQL generated. Use the NOPRINT option on PROC CONTENTS and PROC SQL to avoid cluttering your SAS output. Using comma as the delimiter makes the string harder to use in macro logic (that is why I used Qupcase function) and normal SAS code.  Why not use a space as the delimiter instead? I would NOT use the %RETURN macro command as it violates the principle of single entry and single exit for program blocks. ... View more

This page has programs which return lists of various items http://www.sascommunity.org/wiki/Making_Lists Ron Fehd  lists maven ... View more

Indeed, my assumption was : no underscore -> nothing to trim. - PG ... View more

That's the way it should be. If you want all lines from h then you should ask for a right join instead. - PG ... View more

ZRick wrote: Because we are developing production system using SAS. We need to have the production system that is: robust that is less error prone scalable that can handle small / large volume of data versatile that meet all kinds of different needs with different sets of parameters portable that have the design of object oriented rather than procedure oriented Once upon a time I would have suggested SAS FSP/AF as that product combination is object oriented. ... View more

Quentin, In complex situations, you might want to specify the scope of a macro variable when it is referenced.  Picture macros calling macros, with the entire system written by a team of programmers.  When you retrieve &NAME, you might want to guarantee that it is the value coming from the global symbol table.  You can't do that by utilizing the macro variable reference, but you can do it.  SASHELP.VMACRO contains enough information to let you retrieve &NAME, with scope='GLOBAL'. ... View more

the simulation result is 500 million rows, then it needs to be aggregated via slice and dice of the 500 million rows, which generates about 20 million rows of data, which is then outputed into a sas temp file. Then we use summary stats get min/std/max on that 14 millions of aggregated data. Using sashelp.cars as example, assume it contains 500 million rows. then we aggregate it based all the combination of make, model, type, origin, drivetrain, enginesize, cylinders, horsepower, we could get 14 million rows of this aggregated data. We want to do the stats of this aggregated data. But this 14 million rows of data is outputted into a sas temp file, which creates a huge bottleneck. essentially, it is the derivation of a middle step, this middle step is a sas file. anyway to do entirely in memory? ... View more

Data _Null_ said: > The only difference I see is the first time the "dictionary" is accessed the step takes a bit longer. This factoid is the reason that I stated that accessing sashelp views takes longer. Because, in my testing I put the test against sashelp first I sit corrected: the first access of the dictionaries causes them to be updated, thus, whichever is timed first: * sashelp views * sql dictionary will take longer. I will return to my benchmark suite one of these days to stress test this idea. http://www.sascommunity.org/wiki/Bench_Marking_Results_Comparing_v9-1_and_v9-2 thanks, Krewe you're making this place look like SAS-L! LOL ... View more

I agree with Reeza. And if you need the kind of output you specified (group processing only, no sorting), then you may want to add: NOTSORTED and skip pre-sorting altogether. data t; set t; by sec dt NOTSORTED; if first.dt then n=1; else n+1; run; ... View more