Need help with the code for fetching data from different datasets

[kajal_30]

Hello everyone,

Thank you so much for all the help so far.

I have a new challenge where I need to write a SAS code for below scenario

I have 1 master dataset as below

IDNUM date
1             2401
2            2402
3            2403
4            2404
5            2405

and below are other datasets for providing other info.

dataset_2401

IDNUM RANK
1           12
2          13
3          14
4          15
5           16

dataset_2402

IDNUM RANK
1             21
2            22
3            23
4            24
5 25

dataset_2403

IDNUM RANK
1             31
2            32
3            33
4            34
5            35

dataset_2404

IDNUM RANK
1             41
2            42
3            43
4            45
5            46

dataset_2405

IDNUM RANK
1             51
2            52
3            53
4            55
5            56

For IDNUM=1 I need to take rank from dataset_2401 for IDNUM=2 I need to take rank from dataset_2402 and so on and the new variable as after_rank
basically rank from the table having suffix of date from master dataset and want to append all results in one table.

Can I please get the code in terms of Macro in SAS language. 

Final dataset result

IDNUM after_rank

1                 12

2                22

3                33

4                45

5                56

Regards

kajal

[PaigeMiller]

Are there just the 5 data sets, or are you asking for a macro because there are a lot more than 5? If a lot more than 5, what is the number of datasets, and how would the programmer be able to figure out the number of data sets?

[kajal_30]

it will all be decided from the master dataset the extent of dates available in the master dataset will tell up to what date datasets will be available. Here I have provided only 5 as an example but it could be more even up to 100.

[ballardw]

Is that variable Date and actual SAS date value, a simple number value or character?

Information about that variable is likely to be very critical to specifics on matching.

I believe it has mentioned that example data should be provided in the form of working data steps so that we do not have to guess as to what type of variable is involved.

How many datasets?

How many Idnum values?

Some approaches that  works quickly for 5 sets with 5 id values may not work in a timely fashion for 100 data sets with millions of Ids.

[kajal_30]

variable date is all chars . 

[PaigeMiller]

So how does the programmer know what the number of data sets is? Please explain this. 

I can write this part of the %DO loop

%do i=1 %to 

but I don't know what to put to finish the %DO, because you have not told us how to find the number of data sets. If you can tell us in words, then we can (probably) create the SAS program.

Also, if there are 100 or more data sets, what is the data set name for the 100th data set?

[kajal_30]

it is going be count(IDNUM) from master

[PaigeMiller]

---

@kajal_30 wrote:

it is going be count(IDNUM) from master

---

Please answer my other question: "Also, if there are 100 or more data sets, what is the data set name for the 100th data set?"

[ballardw]

One way that works with your provided examples. Note no MACRO code needed in this approach.

data dataset_2401;
   input IDNUM RANK;
datalines;
1           12
2          13
3          14
4          15
5           16
;
 

data dataset_2402;
   input IDNUM RANK;
datalines;
1             21
2            22
3            23
4            24
5 25
;

data dataset_2403;
   input IDNUM RANK;
datalines;
1             31
2            32
3            33
4            34
5            35
;
data dataset_2404 ;
   input IDNUM RANK;
datalines;
1             41
2            42
3            43
4            45
5            46
;
data dataset_2405;
   input IDNUM RANK;
datalines;
1             51
2            52
3            53
4            55
5            56
;

data tomerge;
   set dataset_2401 - dataset_2405 indsname=inset;
   length date $ 4;
   /* this pulls the "date" from the data set name*/
   date = scan(inset,2,'_');
run;

data master;
   input idnum date :$4.;
datalines;
1             2401
2            2402
3            2403
4            2404
5            2405
;

proc sort data=master;
   by idnum date;
run;
proc sort data=tomerge;
   by idnum date;
run;

data want;
   merge master  (in=inmaster)
         tomerge
   ;
   by idnum date;
   if inmaster;
run;

This is one of the approaches that may not extend well to millions of observations

The Set statement can use lists of various forms. The one shown is a sequential list. If you want all of the datasets that start with "dataset_" you could use "Set dataset_: ;" The colon immediately following the name tells SAS to use all the sets whose names start with "dataset_".

One simple caveat wit the above: the bit that parses the data set name from the Indsname option would need to have a different number in the Scan function if the library name contains any _ or the set names contain more than one _.

[Quentin]

---

@kajal_30 wrote:

Can I please get the code in terms of Macro in SAS language. 

Can you please post the code you have tried, and describe the problem with your code?  Can you please post the sample data as DATA steps with CARDS statements?  This will help others help you.