Add value to an array and create distinct tables based on this value

[Haikuo]

Nice,  ArtC!  rc=h.output (dataset:compress(entity,,'kda')||'(where=(entity="'||entity||'"))');

Something learned today!

Haikuo

[nicnad]

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?

[Haikuo]

'NOTE: No rows were selected.', so please make sure you have a table named 'test' in the library name 'sasuser', OR change the code accordingly.

Haikuo

update: memname and libname need to be upper case. you have 'test' in your log, change to 'TEST' and see what happens.

[nicnad]

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?

[Haikuo]

Hi, Then I suspect that you do need a sort, or like LinLin has suggested, you may have something similar.  Try proc sort, by entity. or directly use my latest code in reply to LinLin's post.

Haikuo

[nicnad]

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".

[Haikuo]

Hmm, my guess is that your 'entity' variable may have some missing value, while table name can't start with number. If that is the case, try to use data set options, such as (where=(not missing(entity)) to get rid of those obs with missing entity, as you don't know where they should belong to anyway.

Haikuo

[joehinson]

This problem seems ideal for the "Hash of Hashes" technique elegantly demonstrated by Richard DeVenezia and Paul Dorfman a while back.

In fact, the technique is so versatile that you don't even need to make a macro out of it. It can handle ANY group size, without any prior knowledge:

data entityinfo;

     infile datalines;

     input entity $ info1 $ info2 $;

     seq=_N_;

datalines;

aaa qwerew werwq

aaa fasds dasd

aaa fsdafas aadf

bbb asdsdfa csdas

bbb fdasfsa cdffas

ccc fasfsf cszcda

;

run;

data _null_;

*---------------------------------------------------------------------------------------------------------

   C R E A T I O N   O F   F I X E D   H A S H   O B J E C T S 

---------------------------------------------------------------------------------------------------------;

     if (1=2) then set entityinfo;

            declare hash infos(ordered:"a");

        infos.defineKey("entity");

        infos.defineData("entity", "ent","hient");

        infos.defineDone();

            declare hiter hinfos("infos");

*---------------------------------------------------------------------------------------------------------

       C R E A T I O N   O F   R E U S A B L E   H A S H   O B J E C T S        

---------------------------------------------------------------------------------------------------------;

            declare hash ent;

            declare hiter hient;

*---------------------------------------------------------------------------------------------------------

    I S O L A T I O N   O F   G R O U P S  

---------------------------------------------------------------------------------------------------------;

            do until(done);

                   set entityinfo end=done;

                           if infos.find() ne 0 then

                                  do;

                   ent=_new_ hash();

                   ent.defineKey("entity","seq");

                   ent.defineData("entity", "seq", "info1","info2");

                   ent.defineDone();

                   hient=_new_ hiter("ent");

                   infos.replace();

                                  end;

              r2check=ent.replace();

       end;*[set entryinfo...];

       call missing(entity, seq, info1, info2);

*---------------------------------------------------------------------------------------------------------

      C R E A T I O N   O F   I N D I V I D U A L   O U T P U T   T A B L E S

---------------------------------------------------------------------------------------------------------;

       do while (hinfos.next() eq 0);

                     do while (hient.next() eq 0);

                     end;*hient;

         ent.output (dataset:entity) ;

       end;*hinfos;

*--------------------------------------------------------------------------------------------------------;

stop;

run;

[Ksharp]

You need to add one more statement to add an obs into the new created ent Hash Table :

do;

                   ent=_new_ hash();

                   ent.defineKey("entity","seq");

                   ent.defineData("entity", "seq", "info1","info2");

                   ent.defineDone();

                   hient=_new_ hiter("ent");

                   ent.replace();

                   infos.replace();

                                  end;

And you also not need to iterate the second hiter Object .

do while (hinfos.next() eq 0);

                     do while (hient.next() eq 0); <--------- It is redundancy .

                     end;*hient;    <--------- It is redundancy .

         ent.output (dataset:entity) ;

       end;*hinfos;

[Ksharp]

By the way, At your second part . It is not reusable Hash Object . It is only a hash table Reference. Variable ent contains lots of different Hash Object's reference.

Ksharp

[joehinson]

Many Thanks, Ksharp, for taking the time to scrutinize the program,

Now I'll address the issues you raised:

(1) There was no need to add an additional statement. The ent.replace was already present, as shown below:

if infos.find() ne 0 then

                                  do;

                   ent=_new_ hash();

                   ent.defineKey("entity","seq");

                   ent.defineData("entity", "seq", "info1","info2");

                   ent.defineDone();

                   hient=_new_ hiter("ent");

                   infos.replace();

                                  end;

              r2check=ent.replace();

(2) The second iteration,

do while (hient.next() eq 0);

                     end;*hient;

yes, can be considered redundant, but harmless! Before, it had an ent.num_items statement within, just for validation (to ensure that the split hash tables had the proper number of rows).

do while (hient.next() eq 0);

rows=ent.num_items;

put rows=;

end;

( I normally do not use the hash-of-hashes technique for splitting tables. Rather, I utilize the approach for PROCESSING SUBGROUPS, and here I usually have plenty of coding).

(3) "REUSABLE HASHES":   This is just semantics. The NAME of the hash object was reusable. Perhaps, I should have labeled that step "RE-CREATABLE" hash object.

I would be happy to elaborate further.

Thanks again!

[Ksharp]

The first of one is right. I overlooked the ent.replace() statement followed infos.replace(). So your code is right too, but logic is a little different with me. Anyway,That is not a matter.

"

"REUSABLE HASHES":   This is just semantics. The NAME of the hash object was reusable. Perhaps, I should have labeled that step "RE-CREATABLE" hash object.

"

That is not a NAME , that is a reference who represent the Hash Object's address in the memory. and ent contains this address. You use this address to operate the real Hash Object in the code. And That skill also cann't call hash-of-hashes . It is more like hash_reference of hash not hash_object of hash .   When you iterate the hash table by using "while(hi.next=0)" ,It is actually copying the different Hash Object's reference into PDV ,then you can use it to output a special dataset .

Ksharp

Message was edited by: xia keshan

[joehinson]

I find it pointless and a total waste of time debating semantics when we can be having intellectual fun discussing algorithms!

To get a better grasp of things, perhaps should look out for my upcoming article in the next issue of Pharmaceutical Programming.