topic Re: ADD value from other tables in SAS Programming

ADD value from other tables

elsfy — Wed, 28 Sep 2022 17:43:29 GMT

Hi,

How can i add the corresponding value from the right table ?

I have a dataset A with ID and DATE variables.

Dataset A :

ID DATE

1 201504

2 201001

and so on...

201504 refers to April, 2015, etc.

I want to add a variable "quantity" to this dataset A.

This variable is available in a list of datasets that all have the same name, except their date. So i have other datasets as :

- TABLE_201001

-TABLE_201002

...

-TABLE_201504

...

These datasets are constructed like this, e.g TABLE_201001 :

ID quantity date

1 445 201001

2 356 201001

...

See here the key merge is ID but also the information about the date.

How can i have sth like this in dataset A with quantity coming from the right table ?

ID DATE QUANTITY

1 201504 445 /*coming from dataset TABLE_201504 */

2 201001 356 /* coming from dataset TABLE_201001 */

I don't want to merge TABLE datasets as i have a lot of them and only showing here an exemple.

Thought of doing a loop with tables and if date values match then adding the value for the ID observation, else keep looking for other datasets.

Re: ADD value from other tables

PeterClemmensen — Wed, 28 Sep 2022 17:51:52 GMT

Do all the TABLE_YYYYMM tables have the same structure?

Re: ADD value from other tables

Reeza — Wed, 28 Sep 2022 18:00:34 GMT

How would add that value for a single value?
Can you show an example of what those tables look like and how that join would work for single table?

You'll need to join with multiple tables and exactly how can vary...multiple ways to do many things as usual.

@elsfy wrote:

Hi,

How can i add the corresponding value from the right table ?

I have a dataset A with ID and DATE variables.

Dataset A :

ID    DATE

1      201504

2      201001

and so on...

201504 refers to April, 2015, etc.

I want to add a variable "quantity" to this dataset A.

This variable is available in a list of datasets that all have the same name, except their date. So i have other datasets as :

- TABLE_201001

-TABLE_201002

...

-TABLE_201504

...

These datasets are constructed like this, e.g TABLE_201001 :

ID   quantity date

1     445    201001

2     356        201001

...

See here the key merge is ID but also the information about the date.

How can i have sth like this in dataset A with quantity coming from the right table ?

ID    DATE       QUANTITY

1      201504    445    /*coming from dataset TABLE_201504 */

2      201001    356    /* coming from dataset TABLE_201001 */

I don't want to merge TABLE datasets as i have a lot of them and only showing here an exemple.

Thought of doing a loop with tables and if date values match then adding the value for the ID observation, else keep looking for other datasets.

Re: ADD value from other tables

elsfy — Wed, 28 Sep 2022 18:08:26 GMT

Yes

Re: ADD value from other tables

PeterClemmensen — Wed, 28 Sep 2022 18:16:14 GMT

Ok. See if you can use this as a template then

data a;
input ID DATE :yymmn6.;
format DATE yymmn6.;
datalines;
1 201504
2 201001
;

data TABLE_201001;
input ID quantity date :yymmn6.;
format date yymmn6.;
datalines;
1 445 201001 
2 356 201001 
;  

data TABLE_201504;
input ID quantity date :yymmn6.;
format date yymmn6.;
datalines;
1 445 201504 
2 356 201504 
;  

data table;
   set table_:;
run;

proc sql;
   create table want as
   select a.*
        , quantity
   from a left join table t
   on  a.ID   = t.ID
   and a.date = t.date
   ;
quit;

Re: ADD value from other tables

elsfy — Wed, 28 Sep 2022 18:18:07 GMT

Don’t know if this answers to your question but i thought of doing sth like this :
Proc sql;
Create table want as
Select a.*,
CASE WHEN a.date = b.date then b.quantity
Else .
End as quantity
From A a
Left join TABLE_YYYYMM b on a.id =b.id
; quit
;
this code can be better if i could loop and search if the information is in TABLE_201010 then add quantity from this table, else keep looking to other tables until it matches the date value in A

Re: ADD value from other tables

ballardw — Wed, 28 Sep 2022 18:21:19 GMT

Do the TABLE_201001 tables have more than one record?

Is this supposed to match on another variable? (Not at all stated) If so state the variable , is that variable duplicated in any of the TABLE_201001 data sets?

Is the value in your data set A actually as SAS date value?? If so, show us what format is currently applied to the variable. If not you likely should create an actual SAS date value as that will allow many things that random number/character combinations impersonating dates won't let you do.

In any case you need to provide details about your existing data sets such as any other matching variables, variable types and possibly formats.

Here is a brief example of combining a couple data sets and pulling part of the data set name as a value in a combined data set.

data example_1;
   input id value;
datalines;
1 23
2 34
;

data example_2;
  input id value;
datalines;
1 14
2 77
;

data combined;
   set example_: indsname=setname;
   indicator = scan(setname,2,'_');
run;

The first two data steps just create data for an example. The third data set combined shows how to combine a lot of similarly named data sets with the : list builder. The : immediately after Example_ tells SAS that you want to use ALL of the data sets whose names start with Example_.

The INDSNAME option adds a temporary variable that holds the names of the data sets, in this case Setname, that will not be added to the data set. So you can use the variable Setname, which will have the library and data set name for parsing. In this case, similar to your Table_201001 sets, the _ separates the stem of the name, Table, from the indicator value 201001. So the Scan function can pull that from the Setname variable using the _ as a delimiter.

Then something like:

data control;
   input id indicator $;
datalines;
1 3
1 2
1 1
2 2
3 2
;

proc sort data=control;
   by id indicator;
run;

proc sort data=combined;
   by id indicator;
run;

data want;
   merge control combined;
   by id indicator;
run;

To add values.

Note: I purposely included values in the Control set that did not have match.

Note also that the Indicator variable in the Control set is Character. That is so the value will match the Indicator made in the Combined set. Which is why we need details of your variables.

If there is NOT a second variable in Control set to match on then this exercise may very well be a complete waste of time without considerably more actual example data as which values to match may not be obvious.

Re: ADD value from other tables

Reeza — Wed, 28 Sep 2022 18:34:29 GMT

@ballardw I think this was merged or added to a different posts by accident?

Re: ADD value from other tables

elsfy — Wed, 28 Sep 2022 18:45:17 GMT

I don’t know why my post is under another

Re: ADD value from other tables

elsfy — Wed, 28 Sep 2022 18:52:57 GMT

Tested your code using only few datasets and it works but one question, this code would be too long if i have many other datasets no ? So what would be the difference with merging all TABLE_YYYYMM together and then adding quantity to table A by ID and date variables ?

data table;
set table_:;
run;

Thank you very much for your help

Re: ADD value from other tables

elsfy — Wed, 28 Sep 2022 19:10:51 GMT

Thank you !
Will give more detail when i can. I can say that date value is written as a numeric variable YYYYMM and datasets Table_YYYYMM have more than one record.
Just a question here it comes back to merging tables if i am correct (Example_ datasets equiv. to Table_YYYYMM) and then matching with the base dataset (dataset A in my case) by ID and date. No? Isn’t there a way without combining datasets?

Re: ADD value from other tables

ballardw — Wed, 28 Sep 2022 19:44:01 GMT

@Reeza wrote:

@ballardw I think this was merged or added to a different posts by accident?

Yes, and I don't know how.