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.
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
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
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!!
try:
data have;
entity='blue car' ;
data have;
set have;
entity=compress(translate(entity,'_',' '));
run;
proc print;run;
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
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?
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
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
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.
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
Haikuo,
how about entity entered as 'aaa','aAA','AAA'?
Thank - Linlin
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;
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.