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

[saskapa]

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

[error_prone]

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.

[RW9 · Accepted solution]

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.

[Ksharp]

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

[saskapa]

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

[Kurt_Bremser]

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

[Kurt_Bremser]

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.

[LinusH]

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

[RW9]

@Kurt_Bremser 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.  

[Astounding]

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;

[Kurt_Bremser]

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

[Astounding]

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.

[LinusH]

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

@Kurt_Bremser 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.

[Kurt_Bremser]

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