topic proc sql in SAS Procedures

proc sql

tomop — Wed, 17 Feb 2010 16:18:06 GMT

Hello I'm a new user.

I've created a number of tables labelled rr1 to rr120. I want to extract columns of data from each one, labelled pred1 to pred120 into one new data file.
I decided the most efficient way to do this would be through the proc SQL function.

What I'm struggling with is how to extract the data from the looping. If I insert the create table instruction within the loop it overwrites 120 times, whereas if I leave it out, I get the query results but no table creation.

Any ideas on how this may be rectified?

Thanks a lot.

Tom

%macro mergingdata;

proc sql;
create tables results as
%do i = 1 %to 120;
select date, pred&i
from rr&i
order by date;
%end;
quit;

%mend;

%mergingdata;
run;
quit;

Re: proc sql

Cynthia_sas — Wed, 17 Feb 2010 16:36:21 GMT

Hi:
I'm a bit confused by your description of the data... You always want the DATE var and then
Option 1: from set RR1, you only want PRED1?? and from set RR2, you only want PRED2??
Option 2: Or, from set RR1 you want PRED1-PRED120??? and from set RR2 you want PRED1-PRED120???

Do the datasets RR1-RR120 only have Date and one other variable each, or do the datasets RR1-RR120 each have Date and PRED1-PRED120????

For your final dataset, you want:
DATE PRED1-PRED120 in an output file called RESULTS??

cynthia

Re: proc sql

tomop — Wed, 17 Feb 2010 16:50:07 GMT

Hello,

Option 1. From RR1 I want the variable pred1, from RR2 I want the variable pred2 etc.. through to 120.

The data sets RR1, have about 120 variables stored inside them, each has a date, but the remainder of the 120 varibles are different.

The final output that you sepcify is excatly as I want it!

Thanks

Tom

Re: proc sql

Cynthia_sas — Thu, 18 Feb 2010 01:13:26 GMT

Hi:
So let's take a look at one possible (theoretical) example of "fake" data from 2 of the files...If the data looked like something like this for RR1 and RR2:
[pre]
RR1
Date PRED1 x1 x2 x3
01/01/2010 111 1 2 3
01/02/2010 222 4 5 6

RR2
Date PRED2 y1 y2 x3
01/01/2010 333 4 5 6
01/02/2010 444 7 8 9
01/03/2010 555 9 8 7
[/pre]

What would you expect the results to look like:
Concatenated Results
[pre]
Date PRED1 PRED2
01/01/2010 111 .
01/01/2010 . 333
01/02/2010 222 .
01/02/2010 . 444
01/03/2010 . 555
[/pre]

Merged or Joined Results
[pre]
Date PRED1 PRED2
01/01/2010 111 333
01/02/2010 222 444
01/03/2010 . 555
[/pre]

The way the data looks and the desired result will determine the method you use to bring the files together, as described here:
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a001081414.htm

cynthia