SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Help with merge

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Help with merge

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;


Accepted Solutions
Solution
‎10-28-2017 11:06 AM
PROC Star
Posts: 1,177

Re: Help with merge

Posted in reply to CalcuScribe

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=p0sz8gq...

 

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

 

Tom

View solution in original post


All Replies
Occasional Contributor
Posts: 6

Re: Help with merge

Posted in reply to CalcuScribe

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

 

Solution
‎10-28-2017 11:06 AM
PROC Star
Posts: 1,177

Re: Help with merge

Posted in reply to CalcuScribe

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=p0sz8gq...

 

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

 

Tom

Occasional Contributor
Posts: 6

Re: Help with merge

Thanks
Super Contributor
Posts: 349

Re: Help with merge

Posted in reply to CalcuScribe

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

Super Contributor
Posts: 349

Re: Help with merge

Posted in reply to CalcuScribe

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?

 

Occasional Contributor
Posts: 6

Re: Help with merge

Posted in reply to andreas_lds

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

Super Contributor
Posts: 349

Re: Help with merge

[ Edited ]
Posted in reply to CalcuScribe

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. 

Contributor
Posts: 25

Re: Help with merge

Posted in reply to CalcuScribe

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;                                                       
        

Occasional Contributor
Posts: 6

Re: Help with merge

Posted in reply to nancy88wilson
Hello
Thank you for your help

Occasional Contributor
Posts: 6

Re: Help with merge

Posted in reply to nancy88wilson

Thank you

Occasional Learner
Posts: 1

Re: Help with merge

Posted in reply to CalcuScribe
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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 636 views
  • 2 likes
  • 5 in conversation