DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

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

Hi,

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

EntityInfo1Info2
aaaqwerewwerwq
aaafasdsdasd
aaafsdafasaadf
bbbasdsdfacsdas
bbbfdasfsacdffas
cccfasfsfcszcda

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.


Accepted Solutions
Solution
‎07-31-2012 04:11 PM
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Super Contributor
Posts: 1,636

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

example:

data have;

input entity $;

cards;

aaa

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

Regular Contributor
Posts: 186

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

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

Super Contributor
Posts: 1,636

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

try:

data have;

entity='blue car' ;

data have;

  set have;

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

run;

proc print;run;

Respected Advisor
Posts: 3,124

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

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

Valued Guide
Posts: 765

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

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?

Respected Advisor
Posts: 3,124

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

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

Super User
Posts: 9,682

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

Mike,

Not really.

Hash Table also can handle unordered dataset , but need some more code. I have no time to code it again.If you want to see it, I can show you.

Ksharp

Regular Contributor
Posts: 186

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

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.

Solution
‎07-31-2012 04:11 PM
Respected Advisor
Posts: 3,124

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

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

Super Contributor
Posts: 1,636

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

Haikuo,

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

Thank - Linlin

Respected Advisor
Posts: 3,124

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

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;

Valued Guide
Posts: 632

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

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;

Respected Advisor
Posts: 3,124

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

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

Valued Guide
Posts: 632

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 27 replies
  • 837 views
  • 6 likes
  • 7 in conversation