topic Re: Split tables in SAS Programming

Split tables

teresa_abbate — Wed, 17 May 2017 09:45:12 GMT

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

Re: Split tables

RW9 — Wed, 17 May 2017 09:56:18 GMT

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

Re: Split tables

Kurt_Bremser — Wed, 17 May 2017 09:56:35 GMT

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.

Re: Split tables

novinosrin — Wed, 17 May 2017 10:04:48 GMT

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

Re: Split tables

novinosrin — Wed, 17 May 2017 11:35:25 GMT

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