topic Re: Help with merge in SAS Data Management

Help with merge

CalcuScribe — Sat, 28 Oct 2017 14:28:03 GMT

I am trying to learn SAS through online instructions and materials

I have written SAS code to merge two data sets: 100stocks.dat and 50stocks.dat

My boss does not think it is complete or optimal. Could a SAS expert please inform me what statements I might have missed and if I have unnecessary statements, especially the RUN statements, in the code below. Thank you.

LIBNAME mydata ‘c:\stocksdata\’

FILENAME prices1 ‘c:\stocksdata\100stocks.dat’

FILENAME prices2 ‘c:\stocksdata\50stocks.dat’

FILENAME prices3 ‘c:\stocksdata\150stocks.dat’

DATA temp1;

Infile prices1;

Input ID 1-10 open 12-20 high 22-30 low 32-40 close 42-50;

RUN;

DATA temp2;

Infile prices2;

Input ID 1-10 open 12-20 high 22-30 low 32-40 close 42-50;

RUN;

DATA prices3;

MERGE temp1 temp2;

BY ID;

RUN;

PROC COPY inlib=work outlib=mydata

SELECT prices3

RUN;

Re: Help with merge

CalcuScribe — Sat, 28 Oct 2017 14:36:49 GMT

I am trying to merge the two data sets vertically so that the merged file has 150 data lines. My guess is I should not be merging by ID but i do not know what attribute i should merge with so that the data stacks vertically

Re: Help with merge

andreas_lds — Sat, 28 Oct 2017 14:47:15 GMT

It is always a good idea to use RUN at the end of each step. If you are not 100% that the data is sorted add proc sort with option presorted before the merge.

proc sort data=temp1 presorted;
  by id;
run;

Does the result of the merge fulfill your expectation?

Re: Help with merge

TomKari — Sat, 28 Oct 2017 14:49:13 GMT

This is a very, very important part of learning SAS. Here's a reference to the section of the documentation that covers your question...it would be well worth it to read it:

http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.2&docsetId=lrcon&docsetTarget=p0sz8gq6nvzcojn13pcqe3twvg1d.htm&locale=en

For your specific question, check the section "Combining SAS Data Sets: Basic Concepts".

Tom

Re: Help with merge

andreas_lds — Sat, 28 Oct 2017 14:50:06 GMT

Use different variable names (except for id) in each dataset, than merge.

Re: Help with merge

CalcuScribe — Sat, 28 Oct 2017 14:51:17 GMT

I do not want the data to be sorted.
i would like to stack the data vertically but i think i am merging the two data sets horizontally

Re: Help with merge

andreas_lds — Sat, 28 Oct 2017 15:00:20 GMT

Please post example data and the expected output.

Following the link @TomKari posted and reading the documentation provided by sas will help you more than getting code from the community.

Re: Help with merge

CalcuScribe — Sat, 28 Oct 2017 15:06:33 GMT

Thanks

Re: Help with merge

nancy88wilson — Tue, 31 Oct 2017 12:20:15 GMT

Perhaps what you want is to concatenate these two files? And do you want your output to be a SAS data set (mydata.prices3) and a new flat file ‘c:\stocksdata\150stocks.dat’ ?

I think something like this would work - check that you ahave a ';' at the ed of each statement, and a RUN after each step.

LIBNAME mydata 'c:\stocksdata' ;
FILENAME prices1 'c:\stocksdata\100stocks.dat' ;
FILENAME prices2 'c:\stocksdata\50stocks.dat' ;
FILENAME prices3 'c:\stocksdata\150stocks.dat' ;

run;

DATA temp1;
Infile prices1;
Input ID 1-10 open 12-20 high 22-30 low 32-40 close 42-50;
RUN;

DATA temp2;
Infile prices2;
Input ID 1-10 open 12-20 high 22-30 low 32-40 close 42-50;
RUN;


DATA mydata.prices3;
set temp1 temp2;
file prices3 ;
put @1 ID
    @9 item;
RUN;

Re: Help with merge

CalcuScribe — Tue, 31 Oct 2017 14:20:57 GMT

Hello
Thank you for your help

Re: Help with merge

CalcuScribe — Tue, 31 Oct 2017 15:11:35 GMT

Thank you

Re: Help with merge

Bayo — Sat, 04 Nov 2017 14:39:54 GMT

i think you need to first sort your files by the same variables you used in the merge proc step. i.e first sort the 3 files by temp1 and temp2 before merging.