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