topic Re: Stacking data of 10 years in SAS Procedures

Stacking data of 10 years

Ashwini_uci — Tue, 04 Nov 2014 19:39:17 GMT

Hi,

I am working on NIS (Nationwide Inpatient Sample) database and I am going to have to combine/stack data for 10 years.

There are numerous variables in this data but a few years do not have certain variables/columns in them so the information for those variables is missing from them.

My question is what would be the best SAS code/procedure to perform this operation of stacking these 10 yrs of data.

is the Data/Set procedure is fine or proc append is approrpiate?

I had used the data/set procedure in the past to stack 3 years of data and all the columns from all 3 years were stacked properly, and the columns were also created in the final stacked version of the data, if any of the years didnot include that particular column/variable which was present in some other year.

I thougtht of asking this question before proceeding with this as I just want to make sure I am heading in a right way with this as it involves a huge amount of data.

Any suggestions, ideas, tips will behighly appreciated.

Thanks!

Ashwini

Re: Stacking data of 10 years

Reeza — Tue, 04 Nov 2014 19:45:39 GMT

proc append with the force option. Data steps read each observation, proc append doesn't.

More important than what variables are present are the variable formats/lengths as you risk truncating data if the lengths of the variables have changed over time.

Re: Stacking data of 10 years

jakarman — Tue, 04 Nov 2014 19:58:46 GMT

Proc append will create a new dataset without over and over replacing the original base dataset.

The datastep set (concat) in one step will create a new dataset in one run. It will be as effective as the proc append.

Both are creating one new big dataset you are going to work on.

Other possible options:

- use a dataetset set concat as view. It will not create a new dataset but at run time read all subsets.

- Use the SPD libname when having more dasd units available. Spreading load over them may increase speed.
All depends on your limitations and the involved sizes on your hardware system. (none of them is specified)

Re: Stacking data of 10 years

ballardw — Tue, 04 Nov 2014 20:31:04 GMT

Before combining this data I would verify:

Variables with the same names are the same type, numeric or character in all data sets. If not, you'll need to address something...

Character variables of the same name have the same length or prepare to deal with that. Both Proc Append and the Data step may yield truncated data if the later appended datasets have longer versions.

That variables of the same name actually represent the same data and coding scheme. Nothing like finding out after doing a bunch of analysis that variable X had a code inserted so that after year Z the value 4 corresponds to a previous value of 3... Or code 6 changed meanings...

That the same topic doesn't have a different variable name from year to year.

What to do about precision if the numbers of significant digits stored has changed between sets, this mostly applies to numbers of decimals.

The longer the time frame the more the data needs to be examined as there are more opportunities for change.

Re: Stacking data of 10 years

Ashwini_uci — Tue, 11 Nov 2014 01:27:46 GMT

Hi,

I tried using Proc append with force option and the log yield errors for appending the first it year's data itself.

I am attaching the log to theoriginal post for your reference. It did not add the variables that were missing in the base databse.

How can I have it add the new variables to the base data and also in the cases if some are missing from the new dataset?

Should I just use data and set step to avoid all these errors?

Thanks!

Re: Stacking data of 10 years

jakarman — Tue, 11 Nov 2014 06:08:32 GMT

Fort the base file with force all intended variables must be there. Adding variables by proc append is not possible only rows.

This is a result of the physical optimization of append doing it that way.

Combining datasets and changing the no of variables will require a complete rewrite of all data (set combines all datasets)

Re: Stacking data of 10 years

Kurt_Bremser — Tue, 11 Nov 2014 08:28:07 GMT

If you do have a dataset for each year (like data01, data02, .... , data14), then

data all;

set

data00

data01

data14

;

run;

will also do it. Variables only present in some datasets will have missing values for the other datasets.

Problems may arise if variables have different types or lengths.

Re: Stacking data of 10 years

Ashwini_uci — Tue, 11 Nov 2014 20:25:22 GMT

I do have data for each year with 90% variables that are same..some have a few additonal ones and some don't /.I will try what you have suggested.

Thanks!

Re: Stacking data of 10 years

LearnByMistk — Wed, 12 Nov 2014 18:24:57 GMT

Better to use set statment to concat data coz in case of append it would remove unmatched column

Re: Stacking data of 10 years

Astounding — Wed, 12 Nov 2014 19:23:12 GMT

You can probably get the best of both worlds by combining Kurt's suggestion with PROC APPEND. First, create an empty data set that is guaranteed to contain all the variables:

options obs=0;

data empty;

set data00 ... data14;

run;

options obs=max;

Reading 0 observations, that will run instantaneously. Then append to the empty data set.

Good luck.

Re: Stacking data of 10 years

Ashwini_uci — Thu, 13 Nov 2014 00:35:05 GMT

Thanks for suggesting this way. It has worked.

I have another question. As far as further analyses is concerned; analyses such as multivariate regression and running some macros on the data, does it matter how the data is stacked, using data/set step or using proc append? Should I be anticipating any warning or errors based on how the data is appended?

The reason I am asking this question because the data /set step really long to append the data compared to the proc append procedure. The proc append took hardly a few minutes to append all 10 yrs of data and the data/set step took almost over an hour to append all the data.

Re: Stacking data of 10 years

jakarman — Thu, 13 Nov 2014 06:27:40 GMT

It looks you are having some IO performance problem in your system setup.

Proc append will read one dataset at the time and write one and there will be no PDV build. As it appends the data there is no rewriting or rereading of the areas that are finished.The result is a data set in de order data is specified.

The datastep will need to build a PDV (conversion step) first opening all datasets and then reading one by one while writing the destination.

If you are needing some analyses split by groups then grouping (ordering) at this stage of building the data is most effective.

Reordering a big dataset could be more effective by joining many smaller ordered ones in a match-merge process

Re: Stacking data of 10 years

Astounding — Fri, 14 Nov 2014 19:28:46 GMT

Under the conditions you described, the analysis data would be identical whether produced by a DATA step or by PROC APPEND. If there were additional statements that were part of the DATA step that would be a different story. But the data will be the same, so the analysis results will be the same as well.

Good luck.

Re: Stacking data of 10 years

Ashwini_uci — Fri, 14 Nov 2014 21:41:49 GMT

I am trying to copy the file from one library to another and I am getting folowing error message:

ERROR: A lock is not available for LIBRARY.NISALL11.DATA.

ERROR: File LIBRARY.NISALL11.DATA has not been saved because copy could not be completed.

This is the file that was created using proc append.

The file created using data/set statement was copied successfully.

I am not sure what the above errros means and how to fix it so that the file is copied successfully.

Re: Stacking data of 10 years

Ashwini_uci — Fri, 14 Nov 2014 22:30:32 GMT

Well, I guess it has nothing to do with how the data is created. I am getting the same error for copying the data created by data/set statement as well.

Re: Stacking data of 10 years

Reeza — Fri, 14 Nov 2014 22:41:10 GMT

It means you have the data open somewhere or it's still being used by some process.

Re: Stacking data of 10 years

jakarman — Sat, 15 Nov 2014 09:42:27 GMT

You did not mention your SAS version or OS type the SAs process is running on.

It could be the Eguide process having opened up the dataset and caused the locking.

Locking is something that can be handled by your OS. When all other possible cause are eliminated this is the nasty one.

By trying to optimize IO it can do the locks at a wrong moment and by that causing error message like your got.

This are very rare events, mostly you will find an other you having accessed that data.

Re: Stacking data of 10 years

Kurt_Bremser — Mon, 17 Nov 2014 06:18:04 GMT

One of the "shortcomings" of proc append is that it fails when the base dataset is opened and can't be locked. Therefore I avoid append in batch jobs, instead combining the datasets in work with a simple data step, then using the OS to delete the base file physically (you can delete a file in UNIX when when opened), and then copy the temp file in work to the base file location.

Re: Stacking data of 10 years

jakarman — Mon, 17 Nov 2014 06:50:16 GMT

Would avoid relying on the often not activated locking process in Unix. In a real multi-user environment locking should be there.

When going for improvements in the OS you will find it is suddenly there.

SAS(R) 9.3 Companion for UNIX Environments filelocks system option 9.3

This file locking is an internal SAS-one. The whole concept of locking is preventing accidents corruption of data.