DATA Step, Macro, Functions and more

How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Hi All,

 

I have a sas dataset that has a label. this label can bee seen when a proc contents is executed.

 

The data is not empty and I would like to make it empty. I can do it with this code for instance :

 

data test; set origin;

if _n_=0;

run;

 

or I can do it  a proc sql by using a creat table.. like   

 

The issue is that whtaever the method I use it remove the label of the sas datasets.  The proof is when I run a proc contents after on it it disappear.

 

Can someone, please provide me with method to empty a sas dataset while keeping the description of the sas dataset intact ?

 

Kind Regards,

 

saskap


Accepted Solutions
Solution
‎08-03-2016 07:29 AM
Super User
Super User
Posts: 7,984

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Empty tables, whilst possible, are not really compliant with SAS.  Even if you don't create any data, there would be a blank observation normally created, this is so the dataaset exists.  SQL can have no observations as that is a different setup - the "tables" don't really exist.  

So why would you need an empty dataset, if there are no observations, don't create a dataset.  If you really need an empty table then you can try:

proc sql;
  delete from HAVE;
quit;

But I would still question the validated of having an empty table.  If your appending things, then first loop creates the dataset, subsequent loops appends.  Various ways round it.

View solution in original post


All Replies
Frequent Contributor
Posts: 149

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

The label is not removed, it is not copied.

 

Removing observations with proc sql will not change the label of that dataset. But afaik that way marks data as deleted and does not remove it from the dataset.

 

 

Solution
‎08-03-2016 07:29 AM
Super User
Super User
Posts: 7,984

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Empty tables, whilst possible, are not really compliant with SAS.  Even if you don't create any data, there would be a blank observation normally created, this is so the dataaset exists.  SQL can have no observations as that is a different setup - the "tables" don't really exist.  

So why would you need an empty dataset, if there are no observations, don't create a dataset.  If you really need an empty table then you can try:

proc sql;
  delete from HAVE;
quit;

But I would still question the validated of having an empty table.  If your appending things, then first loop creates the dataset, subsequent loops appends.  Various ways round it.

Super User
Posts: 10,044

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

data class(label='xxxxxx');
 set sashelp.class;
run;
proc sql;
delete * from class;
quit;
proc contents data=class;run;





Contributor
Posts: 54

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Thanks All

 

The reason why I ask that is that I had some discrepancies issue at the dataset label level when running in two different environment system. So I wanted send this dataset with its label on it  to a third party in order to check the issue,  but because of confidentiality, I need make the dataset empty. 

 

Cheers,

 

saskap

Super User
Posts: 7,853

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Keep in mind that the method you marked as solution only marks the observations as deleted; their content stays in place in the dataset.

Anyone with a hex editor can easily read the data (that you try to keep confidential)!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,853

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

I tested @LinusH's method with this:

data class (label="Testlabel");
set sashelp.class;
run;

proc contents data=class;
run;

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

proc datasets library=work;
delete class;
change want=class;
quit;

proc contents data=class;
run;

It gave me a really empty dataset; only the header information as present.

But I have an additional caveat: I did this with a fresh SAS session, where no other steps had run. I noticed, by inspecting the file created in a previous session where I had done other things before, that data from other steps ends up in the unused memory and appears in the dataset! Looks like SAS (at least 9.2, AIX64) is sloppy cleaning up memory before reuse.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,437

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

I think that SQL DELETE only removes observations logically, which means that the physical size of the empty table remains the same.

My favorite way of creating new tables with the same attributes as an existing one is:

PROC APPEND base=want data=have(obs=0);
RUN;

All attributes are kept, including any constraints and indexes.
The downside is if you wish for the new table ta havery the same name and location, you need to add steps to remove/rename the existing table, and then rename the new one (by using PROC DATASETS).
Data never sleeps
Super User
Super User
Posts: 7,984

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

@KurtBremser is correct.  If you just want to send header information, why send a dataset.  Run a proc contents.  You do have a data transfer in place yes?  If your dataset matches that then there is no problem.  If you don't have data transfer document in place then yes, you will end up with problems.  

 

If you recipient is having problems opening the dataset on thier system, then look at what you have in that header information.  Keep variable names small and using only simple characters.  Labels shouldn't have special codes and such like.  

Super User
Posts: 5,516

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

saskap,

 

You are jumping through many hoops for no reason.  DATA steps do not remove labels.  You can test that for yourself, using the fastest, simplest way to create an empty data set:

 

data have;

name='saskap';

label name='Label should remain in place';

run;

 

data want;

stop;

set have;

run;

 

proc contents data=want;

run;

Super User
Posts: 7,853

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

Posted in reply to Astounding

@Astounding, he wants to preserve the dataset label. Variable labels are preserved, but not the dataset label.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,516

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

[ Edited ]
Posted in reply to KurtBremser

You're right ... I read through this too quickly.

 

Still, I would imagine you can grab the data set label from dictionary.tables, and re-apply it.

Super User
Posts: 5,437

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

@RW9 an empty data set can be used right away as a template. Output from contents must be used in a more manual fashion.

@KurtBremser about your caveat. Assuming that you executed the same in both cases. I don't think that the logic is different between sessions nor versions. Could it be that CLASS is such a small table, and there could be different block sizes that affects the result? If you try a large table you should easily see if the table is empty or not.
Data never sleeps
Super User
Posts: 7,853

Re: How to empty dataset BUT keeping still keeping descriptor portion for dataset label

@LinusHGranted that SASHELP.CLASS is small, but the fact that "foreign" data ends up in an unused portion of the dataset troubles me, securitywise. If the same effect occurs after our transition to 9.4, I will raise an issue with SAS TS. This is how sensitive data has been leaked many times over.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1050 views
  • 7 likes
  • 7 in conversation