topic Re: How to retain indexes after using set statement in SAS Programming

How to retain indexes after using set statement

Swapnil_21 — Tue, 01 Mar 2022 01:57:25 GMT

I have input dataset which has one index, I need to create replica of input and need to maintain the indexes also. But when i use set command, indexes are getting lost.

Here is my input dataset have in inbound library

ACCOUNT_ID	DATA_DATE	TCE	TOT_LMT	SOURCE_SYSTEM_CODE	BASEL_RETAIL_CORP_CODE
RAM3000007	31-Jan-22	1000	3400	RAM	CONS
RAM3000015	31-Jan-22	12000	4600	RAM	CONS

Below is the describe statement

proc sql;
create table INBOUND.CE_RAM_PIT( compress=BINARY bufsize=131072 )
(
ACCOUNT_ID char(30) format=$30. label='Account Identifier',
DATA_DATE num format=DATE9. label='Data Date',
TCE num format=17.2 label='Total Committed Exposure',
TOT_LMT num format=17.2 label='Total Limit',
SOURCE_SYSTEM_CODE char(3) format=$3. label='Source System Code',
BASEL_RETAIL_CORP_CODE char(10) format=$10. label='Basel Retail Corporate Code'

);
create index ECL_MODEL_GROUP_CODE on INBOUND.CE_RAM_PIT(ACCOUNT_ID);
quit;

Now , I need to create new dataset in other directory and retain indexes, when use set indexes are lost.

I have tried Proc datasets , in that case indexes are retained, however, I need to do certain dataset operations using datastep like below

data outbound.CE_RAM_PIT;

set inbound.CE_RAM_PIT;

new_field1=TCE*70;

new_field2=TCE/20;

new_field3=put(new_field2,8.);

new_field4=TOT_LMT/ 3400;

run;

when i run above code, my indexes are lost. Any help on either retaining index or rebuilding indexes using same proc sql would help

Re: How to retain indexes after using set statement

Tom — Tue, 01 Mar 2022 02:45:28 GMT

You are making a new dataset. If you want SAS to make the index then tell it so using the INDEX= dataset option.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/ledsoptsref/n1aaucjme18e43n1jfgz8zsijv88.htm#n1te9tceklywtbn1p0ely8hndc4x

data outbound.CE_RAM_PIT (compress=binary index=(account_no));
  set inbound.CE_RAM_PIT;
...

Note that there is no need to complicate your code by using SQL syntax to create the datasets or the indexes.

data INBOUND.CE_RAM_PIT(compress=BINARY index=(account_id));
  attrib ACCOUNT_ID length=$30 label='Account Identifier';
  attrib DATA_DATE format=DATE9. label='Data Date';
  attrib TCE       format=17.2 label='Total Committed Exposure';
  attrib TOT_LMT   format=17.2 label='Total Limit';
  attrib SOURCE_SYSTEM_CODE length=$3 label='Source System Code';
  attrib BASEL_RETAIL_CORP_CODE length=$3 label='Basel Retail Corporate Code';
  stop;
  call missing(of _all_);
run;

Also there is no need to attach display formats to character strings, SAS already knows how to display character strings.

If your total variables will actually use the 16 significant digits the display formats attached to them imply then you might have issues. SAS floating point numbers can only store about 15 decimal digits precisely.

Re: How to retain indexes after using set statement

Swapnil_21 — Tue, 01 Mar 2022 03:13:19 GMT

Problem is , there are about 16 datasets and I dont know what index is present on which dataset

Re: How to retain indexes after using set statement

Tom — Tue, 01 Mar 2022 03:21:03 GMT

Why do you care?

I almost never use indexes with SAS datasets.

When I do it is part of some large complex project that has dedicated code for creating the datasets.

Re: How to retain indexes after using set statement

andreas_lds — Tue, 01 Mar 2022 07:58:18 GMT

@Swapnil_21 wrote:
Problem is , there are about 16 datasets and I dont know what index is present on which dataset

You could use proc contents or have a look at sashelp.vtable to find the datasets not having an index.

An index is only retained if you are using proc append or proc copy all other procs and the data step won't create an index automatically.

But i concur with @Tom: maintaining indexes is hardly worth the effort.

Re: How to retain indexes after using set statement

LinusH — Tue, 01 Mar 2022 09:59:52 GMT

PROC APPEND with non existent BASE data set should do the trrick.

Re: How to retain indexes after using set statement

Tom — Tue, 01 Mar 2022 13:48:21 GMT

That works great for copy the data (or using OBS=0 dataset option just the structure), but does not support adding the new variables. You might combine it with the ability of PROC SQL to alter a dataset.

Example:

data class (index=(sex));
  set sashelp.class;
run;
proc delete data=new; run;

proc append data=class(obs=0) base=new force;
run;

proc sql;
  alter table new add bmi num label='Body Mass Index';
quit; 

data new ;
  if 0 then modify new;
  set class;
  bmi = (703*weight)/(height*height) ;
  output;
run;

proc contents data=new;
run;

proc print;
  by sex;
run;

But just because it is possible does not mean I would recommend doing that.

Instead:

1) don't bother with indexes unless you really need from.

2) If you have to constantly create new datasets in a particular structure then define a macro (or even just a macro variable) that you can use to generate the code to build the new dataset.

Re: How to retain indexes after using set statement

AllanBowe — Thu, 03 Mar 2022 21:17:48 GMT

This macro can be used for getting the constraints: https://core.sasjs.io/mp__getconstraints_8sas.html

And this one can be used for setting them again: https://core.sasjs.io/mp__createconstraints_8sas.html

Re: How to retain indexes after using set statement

Patrick — Fri, 04 Mar 2022 00:08:30 GMT

@Swapnil_21

Given the column names are you implementing within one of the Risk Stratum solutions? If so then I've created lately a macro that does what you're after. Happy to share if you provide a bit more detail.