Merge every obs in A to each obs in B

[somebody]

I have 2 datasets: A contains trading days and B contain stocks. I would like to create a merged dataset C) that has all trading days for all stocks. How should I do this with a data step?

[novinosrin · Accepted solution]

A sample of both would have been fun to work with.

See if this is what you want

data want;
set b;
do i=1 to nobs;
set a point=i nobs=nobs;
output;
end;
run;

[Shmuel]

Alternatively:

proc sql;
   create table want as 
  select a.*, b.*
  from table1 as a
  full join table2 as b
  on 1;
quit;

[somebody]

thanks, this gives what I wanted. Now I am just trying to understand the code. my question is that what does the command "point=i nobs=nobs" do?

data want;
set b;
do i=1 to nobs; *i assume nobs gives the number of observations of b;
set a point=i nobs=nobs; *append dataset a. What do "point=i nobs=nobs" do?;
output;
end;
run;

[novinosrin]

---

@somebody wrote:

thanks, this gives what I wanted. Now I am just trying to understand the code. my question is that what does the command "point=i nobs=nobs" do?

data want;
set b;
do i=1 to nobs; *i assume nobs gives the number of observations of b;
set a point=i nobs=nobs; *append dataset a. What do "point=i nobs=nobs" do?;
output;
end;
run;

---

HI @somebody , To begin with the logic warrants some kind of N records of table1*M records of table2. This is also typically known as a cartesian in sql sense. The Point= is an option in the set statement that basically points to the variable that has the  observation number in a dataset in the set statement to be read. Since we need to read all the observations in table2, we loop one by one from 1st record 1 to total number of records(nobs= option gets this value at compile time)  in table2. Here the variable I is the so called index that we assign the observation number we read .

So what happens is-->

1. Read a record from B

2. Read all records in a loop one by one in A by pointing to the observation numbers is A given by the index variable I 

3. Output the record read during every loop

4. Repeat the process for all records B

5. Since the End of file is marked for Table B , the datastep process finishes as usual like any another datastep that we are used to. 

HTH

[somebody]

Thank you very much for the detailed response. Can I specify what obs to read in the SET statement? particularly, Instead of getting all trading days for each stock, I would like to merge only the trading days within the Start and End day for each stock (in this case, there are 2 extra variables in the dataset B: Start_day and End_day). I know one way to do this is another DATA step that 1. fills foward the start and end day for each stock and 2.delete the obs if the merged day is outside of the start and end day.

Can I combine this step into the DATA step in your answer? 

[novinosrin]

Hi @somebody   I would request and appreciate you opening a new thread with new specifics rather than here just for the sake of better responses, and keeping it to the requirement. Also that helps wider audience in their search.

All you need to do is post a new thread/question/subject with

1. A sample data of what you HAVE (paste here as text for us to copy paste and work with it)

2. A sample data of what you WANT(your expected output for the input sample)

3. A brief explanation of the convert/business logic and that is your objective

With that, you are in for a buffet. 

[smijoss1]

You are asking for cartesian product. And sql is simplest and best way to do it

[somebody]

thank you for your reply. I tried with PROC SQL but ran into a memory problem due to the size.