Split tables

Reply
Contributor
Posts: 24

Split tables

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_CDAgentCapacity
GROUP11234_145
GROUP11256_160
GROUP13421_120
GROUP21234_130
GROUP21256_110
GROUP23421_115

 

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

AgentValue
1234_145
1256_160
3421_120

 

table for GROUP2

AgentValue
1234_130
1256_110
3421_115

 

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? Smiley Happy

 

Thanks,

Teresa 

Super User
Super User
Posts: 7,942

Re: Split tables

Posted in reply to teresa_abbate

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

Super User
Posts: 7,762

Re: Split tables

Posted in reply to teresa_abbate

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 283

Re: Split tables

Posted in reply to teresa_abbate

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

PROC Star
Posts: 283

Re: Split tables

Posted in reply to novinosrin

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

Ask a Question
Discussion stats
  • 4 replies
  • 127 views
  • 0 likes
  • 4 in conversation