BookmarkSubscribeRSS Feed
tomop
Calcite | Level 5
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;
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
tomop
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 684 views
  • 0 likes
  • 2 in conversation