Hi All,
I'm tryng to create a SAS Macro for implementing the following steps :
1) Read a table (CMS_STG_AGENT) like this:
CAMPAIGN_GROUP_CD | Agent | Capacity |
GROUP1 | 1234_1 | 45 |
GROUP1 | 1256_1 | 60 |
GROUP1 | 3421_1 | 20 |
GROUP2 | 1234_1 | 30 |
GROUP2 | 1256_1 | 10 |
GROUP2 | 3421_1 | 15 |
Number of distinct values and values for campaign_group_cd are unkwown.
For each camapign_group_cd, the agent can have different capacities.
ES. Agent 1234_1 has a capacity of 45 for GROUP1 and 30 for GROUP2.
2) Split the CMS_STG_AGENT into N tables, one for each CAMPAIGN_GROUP_CD.
In this case 2 tables but we could have more than 2 differend CAMPAIGN_GROUP_CD or in other cases just one.
table for GROUP1
Agent | Value |
1234_1 | 45 |
1256_1 | 60 |
3421_1 | 20 |
table for GROUP2
Agent | Value |
1234_1 | 30 |
1256_1 | 10 |
3421_1 | 15 |
3) For each table created, the table's name includes the parameter of CAMPAIGN_GROUP_CD.: CMS_<CAMPAIG_GROUP_CD>.
Es:
The first table's name should be: CMS_GROUP1, for the second one CMS_GROUP2.
Can you help me please? 🙂
Thanks,
Teresa
Simple answer, don't. It is rarely a good idea to split same data out into separate parts. It both increases the complexity of any code you need to write to handle that data, and affects performance - remember to read one file means read header, then read data, if you have many files, thats many header reads.
Please clarify what you need to do as SAS has various different procedures such as by group processing that make these kind of tasks simple and easy coding, for instance, if you need to produce a list of these tables but in the blocks then;
proc report data=have...; by campaign_group_cd; title "The campaign group is: #byval1"; columns ...; run;
The report will automatically then block out your data in the report, and give each a title with the group id per #byval (you can also use #byvar).
The question here is: why?
If you need to process data per groups, do so with by-group processing (or a class statement) in one sweep.
If you need different processes per group, you can always use a where condition. Splitting datasets is usually not necessary.
You don't need a macro, the same can be achieved by using Hashes-->
Assuming your dataset is sorted by CAMPAIGN_GROUP_CD
data _null_;
if _n_=1 then
do;
if 0 then set CMS_STG_AGENT;
dcl hash h(dataset:'CMS_STG_AGENT(obs=0)',multidata: 'y', ordered: 'y');
h.definekey('CAMPAIGN_GROUP_CD');
h.definedata('agent','capacity');
h.definedone();
end;
set CMS_STG_AGENT ;
by CAMPAIGN_GROUP_CD;
if first.CAMPAIGN_GROUP_CD then do;
h.clear();
n+1;
end;
rc=h.add();
if last.CAMPAIGN_GROUP_CD then h.output(dataset:'cms_group'||left(n));
run;
Regards,
Naveen Srinivasan
Using macro, i'd deem this as not efficient:
%macro split_into_datasets;
proc sql;
select distinct CAMPAIGN_GROUP_CD into : list separated by ' '
from CMS_STG_AGENT;
quit;
%do i=1 %to %sysfunc(countw(&list));
%let name=%qscan(&list,&i);
data cms_group&i;
set CMS_STG_AGENT;
where CAMPAIGN_GROUP_CD="&name";
run;
%end;
%mend split_into_datasets;
%split_into_datasets
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.