## Picking specific data point from different libraries

Solved
Occasional Contributor
Posts: 6

# Picking specific data point from different libraries

Considering the following 7 tables, I currently have test.set1, and test.port199001 to test.port199005 (199001 simply refers to all the data from January of 1990, etc). I want to arrive at test.set2. So strictly speaking, I want to fill Var2 of test.set1 with the values from each of the individual test.portXXXXXX based the matching ID and date. How would I be able to do that?? Thank you very much in advance for helping me out!

 test.set1 ID Date Var1 Var2 1 199001 0.424477 . 2 199002 0.919131 . 3 199003 0.188546 . 4 199004 0.294916 . 5 199005 0.589303 . test.port199001 ID Var2 1 0.60478 2 0.696379 3 0.890347 4 0.281266 5 0.682892 test.port199002 ID Var2 1 0.1342 2 0.112347 3 0.129106 4 0.354384 5 0.290145 test.port199003 ID Var2 1 0.776273 2 0.156324 3 0.981456 4 0.006446 5 0.98249 test.port199004 ID Var2 1 0.524929 2 0.037742 3 0.633113 4 0.548542 5 0.324782 test.port199005 ID Var2 1 0.591753 2 0.507835 3 0.078124 4 0.244329 5 0.230047 test.set2 ID Date Var1 Var2 1 199001 0.424477 0.60478 2 199002 0.919131 0.112347 3 199003 0.188546 0.981456 4 199004 0.294916 0.548542 5 199005 0.589303 0.230047

Accepted Solutions
Solution
‎02-15-2012 10:37 PM
Posts: 3,167

## Picking specific data point from different libraries

%macro test;

data test.set2;

%do i=199001 %to 199005;

merge test.set1 test.port&i;

by id;

if date=&i then output;

%end;

run;

%mend;

%test

Regards,

Haikuo

All Replies
Solution
‎02-15-2012 10:37 PM
Posts: 3,167

## Picking specific data point from different libraries

%macro test;

data test.set2;

%do i=199001 %to 199005;

merge test.set1 test.port&i;

by id;

if date=&i then output;

%end;

run;

%mend;

%test

Regards,

Haikuo

PROC Star
Posts: 8,164

## Picking specific data point from different libraries

%macro getdata;

data test.set2;

merge test.set1

%do i=1 %to 5;

test.port19900&i (where=

(id eq &i.))%end;;

by id;

run;

%mend;

%getdata

Super User
Posts: 8,111

## Picking specific data point from different libraries

Combine the datasets and build the "date" variable from the dataset name ;

Then combine with SET1 to make SET2.

data all ;

set test.port199001 - test.port199005 indsname=ds;

date = substr(scan(ds,-1),5);

run;

proc sort;

by id date;

run;

data set2;

merge set1 all;

by id date;

run;

🔒 This topic is solved and locked.