Thank you for the reply your initial code works great with a proc sort. Just to let you know, I tried running the code you provided in reply to LinLin post and I got a bunch a errors. I guess the error is behind the keyboard, but still here are the errors code I received : ERROR: The value '1'n is not a valid SAS name. ERROR: An error has occurred during instance method OM_OUTPUT(505) of "DATASTEP.HASH". NOTE: The data set SASUSER.BLUECAR2 has 1 observations and 18 variables. NOTE: The data set SASUSER.REDBOAT3 has 5134 observations and 18 variables. NOTE: The data set SASUSER.GREENCAR4 has 316 observations and 18 variables. NOTE: The data set SASUSER.NA5 has 2078 observations and 18 variables. ERROR: Invalid data set name at line 42 column 4. ERROR: An error has occurred during instance method OM_OUTPUT(505) of "DATASTEP.HASH". NOTE: The data set SASUSER.YELLOWBOAT8 has 8446 observations and 18 variables. NOTE: The data set SASUSER.REDCAR9 has 13048 observations and 18 variables. ERROR: Invalid data set name at line 42 column 4. ERROR: An error has occurred during instance method OM_OUTPUT(505) of "DATASTEP.HASH". ... View more

Thank you. My bad I did not thought this was case sensitive. Your code works great but for some entity I don't get the right number of records... Your code created a table named "BLUECAR" that contains one observation, but if I run this piece  of code, I get 316 observations  : proc sql; create table test_no_records as select * from sasuser.test where entity = "BLUE CAR" ;quit; Do you have any idea what might be causing this issue? ... View more

Thank you for your help. I get the following error while running your code : Some of your options or statements may not be supported with the Activex or Java series of devices.  Graph defaults for these       drivers may be different from other SAS/GRAPH device drivers.  For further information, please contact Technical Support. 9          OPTIONS DEV=ACTIVEX; 10         FILENAME EGHTML TEMP; NOTE: Writing HTML(EGHTML) Body file: EGHTML 11         ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault 11       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css") 11       ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation 11       ! ; 12         13         %gaccessible; 14         15         16         proc sql NOPRINT; 17         18         select quote(CATS(name)) into :var separated by ',' from dictionary.columns where libname='SASUSER' AND 18       ! MEMNAME='test'; NOTE: No rows were selected. 19         20         quit; NOTE: PROCEDURE SQL used (Total process time):       real time           0.00 seconds       cpu time            0.01 seconds       21         22         23         data _null_; 24         25            dcl hash h(ordered: 'a'); 26         27            h.definekey('_n_'); 28         29            h.definedata (&VAR);                             _                             22 WARNING: Apparent symbolic reference VAR not resolved. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,               a missing value, INPUT, PUT.  30         31            h.definedone(); 32         33         34            do _n_=1 by 1 until (last.entity); 35         36               set SASUSER.test; 37         38               by entity notsorted; 39         2                                                          The SAS System                               14:36 Tuesday, July 31, 2012 40               h.add(); 41         42            end; 43         44            h.output (dataset:'work.'||compress(entity,,'kda')); 45         46            run; NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time):       real time           0.01 seconds       cpu time            0.00 seconds       47         48         49         50         51         %LET _CLIENTTASKLABEL=; 52         %LET _EGTASKLABEL=; 53         %LET _CLIENTPROJECTNAME=; 54         %LET _SASPROGRAMFILE=; 55         56         ;*';*";*/;quit;run; 57         ODS _ALL_ CLOSE; 58         59         60         QUIT; RUN; 61         Can you please help me fix this? ... View more

Thank you for the quick reply hai.kuo Two things : Is there a way that the variables get defined automatically. I.E this line : h.definedata ('entity','info1','info2'); The thing is my real tables have 20+ variables and the variables are subject to change so I would not want to rewrite the code each time. Also, the same problem that I mention to Linlin seem to happen with your code. The value given to the variable "entity" is composed of multiple words seperated by a space, but your code only takes the first word : E.G. Entity blue car Your code will create a table named blue and the table is empty since no entity has the value blue... Hope my explanations are clear. Again thank you both for your help and quick answers. ... View more

Thank you for you quick reply Linlin. Your code works great for my example the only thing is that in my real tables the observations have spaces in the text string so the where statement is not applied properly. E.G. Entity aaa blue car red boat And the filter create table named aaa, blue, red... it does not the value as a whole because of the space. Can you please help me fix this? Hope you understand my explanation. thank you for your help!! ... View more