BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leonh
Calcite | Level 5

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

IDDateVar1Var2
11990010.424477.
21990020.919131.
31990030.188546.
41990040.294916.
51990050.589303.
test.port199001

IDVar2

10.60478

20.696379

30.890347

40.281266

50.682892

test.port199002

IDVar2

10.1342

20.112347

30.129106

40.354384

50.290145

test.port199003

IDVar2

10.776273

20.156324

30.981456

40.006446

50.98249

test.port199004

IDVar2

10.524929

20.037742

30.633113

40.548542

50.324782

test.port199005

IDVar2

10.591753

20.507835

30.078124

40.244329

50.230047

test.set2

IDDateVar1Var2
11990010.4244770.60478
21990020.9191310.112347
31990030.1885460.981456
41990040.2949160.548542
51990050.5893030.230047
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

%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

View solution in original post

3 REPLIES 3
Haikuo
Onyx | Level 15

%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

art297
Opal | Level 21

%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

Tom
Super User Tom
Super User

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;

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
  • 704 views
  • 6 likes
  • 4 in conversation