topic Re: how to add new variable to a spde dataset in SAS Enterprise Guide

how to add new variable to a spde dataset

alepage — Tue, 02 Feb 2021 19:21:57 GMT

Hello,

I am using a proc copy to copy a spde dataset from staging to dev (just the data structure).

Here's the code:

libname sauto spde 'path1';

libname sprop spde 'path2';

libname dauto spde 'path3';

libname dprop spde 'path4';

options obs=0;
proc copy in=sauto out=dauto;
select wc_auto_inforcesep2020 ;
run;

Is there a way to add a new variable such as format newvar $2. to a spde dataset, maybe with proc copy????

Thanks in advance for your help

Re: how to add new variable to a spde dataset

LinusH — Wed, 03 Feb 2021 09:33:46 GMT

There is no real difference between managing SPDE and Base SAS data sets.

The sImple answer is no.

What you can do is do the PROC COPY, and then SQL ALTER TABLE.

The table will be recreated, but that's probbaly fine since it's just the structure, not a mssive amount of data.

Re: how to add new variable to a spde dataset

Kurt_Bremser — Wed, 03 Feb 2021 10:12:25 GMT

@LinusH wrote:

The table will be recreated, but that's probbaly fine since it's just the structure, not a mssive amount of data.

It will rewrite the whole table; with SAS, any structural change requires this.

Given the relative performance of SPDE libraries (thanks to threading), this will be considerably faster than changing a V9 dataset.

I just ran a test with a larger dataset here; creating the dataset by copying from the V9 engine took 43 seconds, adding the column 15 seconds.

Re: how to add new variable to a spde dataset

alepage — Thu, 04 Feb 2021 14:46:37 GMT

Hello Mr. Bremser,
I am sorry but it is not clear for me. Did you find a way to add a new variable using proc copy or you have use a data datasetname1; set datasetname; format newvar $2.;run; If there is a way to do it with the proc copy, could you please provide me an example.

Best Regards,

Re: how to add new variable to a spde dataset

Kurt_Bremser — Thu, 04 Feb 2021 14:56:01 GMT

No, you can't make any structural changes with proc copy. For this, you need a data step, or COPY and then ALTER TABLE in SQL. My observation was that ALTER TABLE still needs to rewrite the whole dataset, so, performancewise, you will be better off by using a DATA step to copy a dataset to SPDE and add a variable on the way.