topic Re: Add value to an array and create distinct tables based on this value in SAS Programming

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

nicnad — Tue, 31 Jul 2012 19:06:14 GMT

Hi,

Lets say I have the following table and that this table is named test. It is located in sasuser.test

Entity	Info1	Info2
aaa	qwerew	werwq
aaa	fasds	dasd
aaa	fsdafas	aadf
bbb	asdsdfa	csdas
bbb	fdasfsa	cdffas
ccc	fasfsf	cszcda

I would want to create a macro that would programatically evaluate what are the different entity and create a distinct table for each entity. The table would also be named by the name of the entity

The result would be :

create table sasuser.aaa as select * from sasuser.test as test where test.entity = "aaa"

create table sasuser.bbb as select * from sasuser.test as test where test.entity = "bbb"

create table sasuser.ccc as select * from sasuser.test as test where test.entity = "ccc"

My only concern is how do I write the code that would create an array that would loop through all the values in column entity and keep each unique entity and then create a table where the name of the table is the entity and the filter is the entity?

Please note that the values are example and will be subject to change, this is why I want to have a macro that will do the work I don't want to have static reference.

Hope my explanations are clear.

Thank you for your help and time.

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

Linlin — Tue, 31 Jul 2012 19:26:13 GMT

example:

data have;

input entity $;

cards;

aaa

Aaa

bbb

BBB

ccc

;

proc sql noprint;

select distinct(upcase(entity)) into :list separated by ' '

from have;

quit;

%macro test;

proc sql;

%do i=1 %to %sysfunc(countw(&list));

%let dsn=%scan(&list,&i);

create table &dsn as

select * from have

where upcase(entity)="&dsn";

%end;

quit;

%mend;

%test

Linlin

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

Haikuo — Tue, 31 Jul 2012 19:27:52 GMT

Why Macro when there is something out there far more efficient?

data _null_;

dcl hash h(ordered: 'a');

h.definekey('_n_');

h.definedata ('entity','info1','info2');

h.definedone();

do _n_=1 by 1 until (last.entity);

set sasuser.test;

by entity notsorted;

h.add();

end;

h.output (dataset:'sasuser.'||compress(entity,,'kda'));

run;

Haikuo

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

nicnad — Tue, 31 Jul 2012 19:41:06 GMT

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!!

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

Linlin — Tue, 31 Jul 2012 19:50:11 GMT

try:

data have;

entity='blue car' ;

data have;

set have;

entity=compress(translate(entity,'_',' '));

run;

proc print;run;

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

MikeZdeb — Tue, 31 Jul 2012 19:59:23 GMT

hi ... to work properly, the hash solution does depend on the original data set having all values of the same entity in consecutive observations in the original data (not necessarily sorted, but grouped) ... yes/no?

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

nicnad — Tue, 31 Jul 2012 20:02:51 GMT

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.

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

Haikuo — Tue, 31 Jul 2012 20:05:24 GMT

Yes, Mike. That is surely an assumption of my solution, which I failed to elaborate in detail. OP, to safely use hash() here, a presort may give you peace of mind unless you are sure about your data's clustering or grouping.

Haikuo

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

Haikuo — Tue, 31 Jul 2012 20:11:51 GMT

Well, that case will need a macro variable (not macro though), new code has also addressed your second question.

proc sql NOPRINT;

select quote(CATS(name)) into :var separated by ',' from dictionary.columns where libname='SASUSER' AND MEMNAME='TEST';

quit;

data _null_;

dcl hash h(ordered: 'a');

h.definekey('_n_');

h.definedata (&VAR);

h.definedone();

do _n_=1 by 1 until (last.entity);

set SASUSER.TEST;

by entity notsorted;

h.add();

end;

h.output (dataset:'sasuser.'||compress(entity,,'kda'));

run;

Haikuo

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

Linlin — Tue, 31 Jul 2012 20:15:05 GMT

Haikuo,

how about entity entered as 'aaa','aAA','AAA'?

Thank - Linlin

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

Haikuo — Tue, 31 Jul 2012 20:26:40 GMT

Good question, Linlin. I believe ultimately it is up to OP to decide. From my end, it is easy, just add a series number:

h.output (dataset:'sasuser.'||compress(entity,,'kda')||left(_n_));

Haikuo

update: my bad, it should be the following, '_n_' is tricky here.

data _null_;

dcl hash h(ordered: 'a');

h.definekey('_n_');

h.definedata (&VAR);

h.definedone();

m=_n_;

do _n_=1 by 1 until (last.entity);

set SASUSER.TEST;

by entity notsorted;

h.add();

end;

h.output (dataset:'sasuser.'||compress(entity,,'kda')||left(m));

run;

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

nicnad — Tue, 31 Jul 2012 20:31:24 GMT

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?

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

Haikuo — Tue, 31 Jul 2012 20:35:39 GMT

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

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

nicnad — Tue, 31 Jul 2012 20:48:42 GMT

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?

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

Haikuo — Tue, 31 Jul 2012 20:58:41 GMT

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

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

nicnad — Tue, 31 Jul 2012 21:26:38 GMT

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

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

Haikuo — Tue, 31 Jul 2012 21:49:27 GMT

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

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

ArtC — Wed, 01 Aug 2012 01:20:54 GMT

haikuo,

Good idea to use the hash object. The following alternate approach utilizes the HITER object and IMO simplifies the DATA step a bit.

data _null_;
   if 0 then set have;
   dcl hash h(dataset:'have', ordered: 'a');
   h.definekey('entity');
   h.definedata ('entity','info1','info2');
   h.definedone();
   dcl hiter hh('h');

   do while(hh.next()=0);
      rc=h.output (dataset:compress(entity,,'kda'));
   end;
   stop;
   run;

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

Haikuo — Wed, 01 Aug 2012 01:40:03 GMT

Hi ArtC,

I have test run your code, however, when hash() is loaded, it only keeps unique record by key (unless 'multidata' option is added), therefore it does not deliver what OP wanted. It basically output multiple tables with the same exact contents. I could not figure out how to achieve the same goal using hiter without involving another hash() , with which all of the obs are loaded, then use hiter to retrieve and output.

Please see below:

data have;

input (Entity Info1 Info2) (:$);

cards;

aaa qwerew werwq

aaa fasds dasd

aaa fsdafas aadf

bbb asdsdfa csdas

bbb fdasfsa cdffas

ccc fasfsf cszcda

;

data _null_;

if 0 then set have;

dcl hash h(dataset:'have', ordered: 'a');

h.definekey('entity');

h.definedata ('entity','info1','info2');

h.definedone();

dcl hiter hh('h');

do while(hh.next()=0);

rc=h.output (dataset:compress(entity,,'kda'));

end;

stop;

run;

Best Regards,

Haikuo

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

ArtC — Wed, 01 Aug 2012 02:55:47 GMT

Good catch Haikuo,

This one has a couple fixes.

data _null_;
   if 0 then set have;
   dcl hash h(dataset:'have', ordered: 'a', multidata:'Yes');
   h.definekey('entity');
   h.definedata ('entity','info1','info2');
   h.definedone();
   dcl hiter hh('h');
   length value $8;

   value=' ';
   do while(hh.next()=0);
      if value ne entity then rc=h.output (dataset:compress(entity,,'kda')||'(where=(entity="'||entity||'"))');
      value=entity;
   end;
   stop;
   run;