DATA Step, Macro, Functions and more

Deleting data rows without affecting indexes or keys

Reply
Occasional Contributor
Posts: 8

Deleting data rows without affecting indexes or keys

Hello there,

 

I'm a newbie with SAS archiving of data. I would like to know how can I delete older datas w/o affecting indexes and account/party keys.

I need to delete data w/ year 2010 and below. The account/party resets to 0,1,2,3,4 so on so forth where in it should start to different count. I hope I make sense. 

 

Regards,

kenjots

Super User
Posts: 5,437

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239
Not really.
Try to exemplify with a sample.
Data never sleeps
Occasional Contributor
Posts: 8

Re: Deleting data rows without affecting indexes or keys

Let say I have this kind of data. Take note that account party is auto increment.

ACCOUNT_PARTY NAME DATE
1 ALPHA 2017
2 BETA 2016
3 CHARLIE 2018
4 DELTA 2015
5 FOXTROT 2016


I would like to select all data where year > 2016. So that output should be like this

ACCOUNT_PARTY NAME DATE
1 ALPHA 2017
3 CHARLIE 2018

However, upon opening the temporary dataset in work library, the output is like this.

ACCOUNT_PARTY NAME DATE
1 ALPHA 2017
2 CHARLIE 2018

The account party resets to normal count.
PROC Star
Posts: 1,760

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239

There is no auto increment field in SAS.

So you are probably using Oracle data or similar.

If you don't want to value to change, you have to turn off in auto-increment.

You can't ask for the field to be populated automatically and for the field not to change.

Respected Advisor
Posts: 4,173

Re: Deleting data rows without affecting indexes or keys

[ Edited ]
Posted in reply to brutal3239

@brutal3239

That's certainly not a SAS table unless you're telling us only a very scalled down and distorted part of the story. If you're interfacing with a database then what's the database?

If it's Oracle then there would be options to implement an autoincrement field which generates a key for you which doesn't change (i.e. using a sequencer object in an insert trigger; the newer versions of Oracle also have a specific new field type for such a requirement).

 

...but as @ChrisNZ already hints: That's something you need to solve on the database side.

Occasional Contributor
Posts: 8

Re: Deleting data rows without affecting indexes or keys

As mentioned, it is a sas dataset.  I would like to know how to delete rows w/o affecting the indexes?

Super User
Posts: 5,437

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239
You need to show us the log from this operation including the libname statement.
Data never sleeps
Occasional Contributor
Posts: 8

Re: Deleting data rows without affecting indexes or keys

[ Edited ]

Looks like this but again, the party_key count will reset to 0,1,2,3 so on so forth. I want to retain the party_key index.

 

proc sql;
create table tmp as
select * from core.transaction_table where
month_key <= 201201
;
quit;

 

 

Frequent Contributor
Posts: 149

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239

What are you talking about? The code you posted creates a new dataset named "tmp", there is no code creating an index.

 

The concept "index" is described here.

 

Super User
Super User
Posts: 7,076

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239

Can you explain what you are talking about?  SAS will not change any data values if you delete observations.

data have ;
  length ACCOUNT_PARTY 8 NAME $20 DATE 8;
  input ACCOUNT_PARTY NAME DATE ;
cards;
1 ALPHA 2017
2 BETA 2016
3 CHARLIE 2018
4 DELTA 2015
5 FOXTROT 2016
;

proc sort data=have ;
  by date ;
run;
proc print data=have ;
run;
proc print data=have ;
  where date > 2016 ;
run;

image.png

Occasional Contributor
Posts: 8

Re: Deleting data rows without affecting indexes or keys

Hi Tom,

 

Would it be okay if I message/email you our sample data?

 

Regards,

brutal3239

Respected Advisor
Posts: 4,173

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239

@brutal3239

If there is nothing confidential in your sample data then you can also attach them here.

Occasional Contributor
Posts: 8

Re: Deleting data rows without affecting indexes or keys

After deleting rows, how am i going to compress the dataset? It is still 17GB albeit more than 500k rows were deleted. 

 

I've tried this,

 

proc sql;

create table tmp 

(

COMPRESS=YES

) as

***then delete rows****

;

quit;

 

It worked. However, it is in work library. I want to do it directly to the dataset where I am going to delete rows/data.

 

Thanks,

brutal3239

Respected Advisor
Posts: 4,173

Re: Deleting data rows without affecting indexes or keys

Posted in reply to brutal3239

@brutal3239

"It worked. However, it is in work library. I want to do it directly to the dataset where I am going to delete rows/data."

If you want it in a permanent library then simply create the new table in a permanent library: create table <libref>.<table name> as ...

 

"I want to do it directly to the dataset "

A Proc SQL/DELETE will only logically delete the rows. If you want the data set size reduced then you need to re-create the table (i.e. using your current SQL CREATE statement).

Ask a Question
Discussion stats
  • 13 replies
  • 177 views
  • 0 likes
  • 6 in conversation