BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nicnad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

27 REPLIES 27
Linlin
Lapis Lazuli | Level 10

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

nicnad
Fluorite | Level 6

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

Linlin
Lapis Lazuli | Level 10

try:

data have;

entity='blue car' ;

data have;

  set have;

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

run;

proc print;run;

Haikuo
Onyx | Level 15

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

MikeZdeb
Rhodochrosite | Level 12

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?

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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

nicnad
Fluorite | Level 6

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.

Haikuo
Onyx | Level 15

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

Linlin
Lapis Lazuli | Level 10

Haikuo,

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

Thank - Linlin

Haikuo
Onyx | Level 15

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;

ArtC
Rhodochrosite | Level 12

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;

Haikuo
Onyx | Level 15

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

ArtC
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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