05-17-2017 05:45 AM
I'm tryng to create a SAS Macro for implementing the following steps :
1) Read a table (CMS_STG_AGENT) like this:
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
table for GROUP2
3) For each table created, the table's name includes the parameter of CAMPAIGN_GROUP_CD.: CMS_<CAMPAIG_GROUP_CD>.
The first table's name should be: CMS_GROUP1, for the second one CMS_GROUP2.
Can you help me please?
05-17-2017 05:56 AM
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).
05-17-2017 05:56 AM
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.
05-17-2017 06:04 AM
You don't need a macro, the same can be achieved by using Hashes-->
Assuming your dataset is sorted by CAMPAIGN_GROUP_CD
if _n_=1 then
if 0 then set CMS_STG_AGENT;
dcl hash h(dataset:'CMS_STG_AGENT(obs=0)',multidata: 'y', ordered: 'y');
set CMS_STG_AGENT ;
if first.CAMPAIGN_GROUP_CD then do;
if last.CAMPAIGN_GROUP_CD then h.output(dataset:'cms_group'||left(n));
05-17-2017 07:35 AM
Using macro, i'd deem this as not efficient:
select distinct CAMPAIGN_GROUP_CD into : list separated by ' '
%do i=1 %to %sysfunc(countw(&list));