topic Re: adding data in SAS Data Management

adding data

Solvej — Mon, 29 Oct 2018 10:50:40 GMT

Dear SAS experts

I hope somebody can help solve a data management problem. I have to datasets (have1 and have2) that I want to merge based on two variables (record_id and number), however I dont want the first dataset have1 to be overwritten but instead data to be added where missing. I hope this makes sense. Where data have been added in the want dataset it has been marked with bold.

Kind regards Solvej

Have1

record_id

number

Have2

record_id

number

Want

record_id

number

Re: adding data

RW9 — Mon, 29 Oct 2018 11:18:06 GMT

Post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

As such, I will just give examples.

You can use update:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202975.htm

need to expand the first dataset.

Or you can normalise the data - something you should really do anyway - set the two together, renumber, then transpose again.

Or you can use arrays and renames.

Re: adding data

Solvej — Mon, 29 Oct 2018 13:13:35 GMT

Hi again

Sorrry about the missing data. Here it is:

data WORK.HAVE1;
infile datalines dsd truncover;
input record_id:BEST. number:BEST. A1:BEST. B1:$1. C1:$1. A2:BEST. B2:$1. C2:$1.;
format record_id BEST. number BEST. A1 BEST. A2 BEST.;
label record_id="record_id" number="number" A1="A1" B1="B1" C1="C1" A2="A2" B2="B2" C2="C2";
datalines;
1 17 1 c c 8 t t
2 2 . .
3 1 8 f f .

data WORK.HAVE2;
infile datalines dsd truncover;
input record_id:BEST. number:BEST. A1:BEST. B1:$1. C1:$1. A2:BEST. B2:$1. C2:$1. A3:BEST. B3:$2. C3:$2.;
format record_id BEST. number BEST. A1 BEST. A2 BEST. A3 BEST.;
label record_id="record_id" number="number" A1="A1" B1="B1" C1="C1" A2="A2" B2="B2" C2="C2" A3="A3" B3="B3" C3="C3";
datalines;
1 17 2 b w 4 s h 6 kk gg
2 2 3 s x 5 s g .
3 1 4 x x . .

Re: adding data

Astounding — Mon, 29 Oct 2018 14:34:19 GMT

For data management purposes, I don't think you found the proper structure to the data. Here is an alternative that keeps all the original information but is much more flexible for programming purposes. (I'm illustrating just for one of the RECORD_IDs, and you may need to learn some programming techniques to deal with this form of the data):

Record_ID Number Source Record A B C

3 1 have1 1 8 f f

3 1 have2 2 4 x x